
import { SharedService } from './../shared/shared.service';
import { Inject, Injectable } from '@nestjs/common';
import {
    CntrLeaseDto,
    CntrLeaseRateDto,
    CreateCntrDto,
    DeletePortCntrStatDto,
    DeletePortCntrStatsDto,
    GateOutDto,
    InPlanDto,
    InplanImportCntrDto,
    LeasePlanDto,
    OutPlanDto,
    PlanDto,
    PortCntrStatDto,
    QueryPlanCntrsDto,
    ReleaseFormDto,
    RentPlanDto,
    ReplyCntrFixDto,
} from './dto/create-container.dto';
import { DataSource, QueryRunner, Brackets, SelectQueryBuilder } from 'typeorm';
import { CrudService } from '@/shared/crud.service';
import { InjectDataSource } from '@nestjs/typeorm';
import { DeleteDto, ResponseJson, SqlQueryDto } from '@/common/type/app.dto';
import {
    CntrInPlan,
    CntrOutPlan,
    Cntr,
    CntrPlanFee,
    VCntr,
    VInventory,
    VInventoryGroup,
    CntrPlan,
    CntrLease,
    CntrLeasePlan,
    VCntrLease,
    LeaseForm,
    CntrPlanCntr,
    FixReply,
    CntrFixItem,
    VCurrentCntr,
    CntrRentPlan,
    PortCntrStat,
    VFixQuotation,
} from './entities/container.entity';
import { ResponseCode } from '@/common/type/enum';
import { forEachRight, isNil, omit } from 'lodash';
import {
    existsSync,
    mkdirSync,
    createWriteStream,
    unlinkSync,
    readFileSync,
    writeFileSync,
} from 'node:fs';
import axios from 'axios';
import * as XLSX from 'xlsx';
import { v4 as uuidv4 } from 'uuid';
import { FeeDet, FeeTyp, Invoice } from '@/business/entities/business.entity';
import {
    LeaseCntrBackDto,
    UpdateCntrDto,
    UpdateInPlanDto,
    UpdateLeaseCntrDto,
    UpdateLeasePlanDto,
    UpdateOutPlanDto,
    UpdatePlanFeeDto,
    UpdateRentPlanDto,
    UpdateTransPlanDto,
} from './dto/update-container.dto';
import { Client, CntrStat, CntrStatLogic, CyCfs, Scode } from '@/basedata/entities/basedatum.entity';
import { BusiReportDto, FeedetDto, LeaseReportDto } from '@/business/dto/create-business.dto';
import { BusinessService } from '@/business/business.service';
import { checkCntrNo } from '@/common/tool/cfstools';
import { validate } from 'class-validator';
import { plainToInstance } from 'class-transformer';
import { checkDto } from '@/common/tool/dbhelper';
import { join } from 'node:path';
import * as dayjs from 'dayjs';
import * as Excel from 'exceljs';
import * as libre from 'libreoffice-convert';
import * as util from 'node:util';
import { EmailType, EmailResultType } from '@/common/type/app';
import { EIDXJC, EITZJC, EIZYXT, EOQZCC, EOZYXC, UUSTAR } from '@/common/const/CntrStat';
import { removeProperty, getMonthsBetween, deleteAllProperties, formatDateTime } from '@/common/tool/trans';

(libre as any)['convertAsync'] = util.promisify(libre.convert);

@Injectable()
export class ContainerService {
    constructor(
        @InjectDataSource() private datasource: DataSource,
        private businessService: BusinessService,
        private crud: CrudService,
        @Inject('downloadPath') private readonly downloadpath: string,
        private sharedService: SharedService,
    ) { }

    async cntrCreateS(
        cntrObj: CreateCntrDto,
    ): Promise<ResponseJson<Cntr, null>> {
        const cntrId = await this.createCntr(
            this.datasource.createQueryRunner(),
            cntrObj,
        );
        const cntr = await this.datasource
            .createQueryBuilder<Cntr>(Cntr, 'cntr_file')
            .where('id = :id', { id: cntrId })
            .getOne();

        return {
            code: ResponseCode.SuccessWithInfo,
            msg: ['添加成功'],
            obj: cntr,
        };
    }
    /* async cntrUpdateS(
        cntrObj: UpdateCntrDto,
    ): Promise<ResponseJson<Cntr, null>> {
        const updateFlag = await this.updateCntr(cntrObj);
        const cntr = await this.datasource
            .createQueryBuilder<Cntr>(Cntr, 'cntr_file')
            .where('id = :id', { id: cntrObj.id })
            .getOne();

        return {
            code: ResponseCode.SuccessWithInfo,
            msg: ['修改成功'],
            obj: cntr,
        };
    } */

    /**
     * 生成箱动态
     * @param dto 
     * @param queryRunner 
     */
    async createPortCntrStat(dto: PortCntrStatDto, queryRunner: QueryRunner) {
        const errors = await validate(plainToInstance(PortCntrStatDto, dto));
        if (errors.length > 0) {
            throw new Error(Object.values(errors[0].constraints).join('   '));
        }
        // 检查是否合法动态时间
        const GateInTimeCheck = await queryRunner.manager.createQueryBuilder()
            .select(['code'])
            .from(Scode, 's_code')
            .where('fld_eng = :fld_eng', { fld_eng: 'GATEIN_TODAY_CHECK' })
            .getRawOne();

        if (GateInTimeCheck?.code === 'Y') {
            if (new Date(dto.stat_tim).toDateString() !== new Date().toDateString()) {
                throw new Error(`动态日期与当前日期不一致`);
            }
        }
        /* if (new Date(dto.stat_tim).toDateString() !== new Date().toDateString()) {
            throw new Error(`动态日期与当前日期不一致`);
        } */
        // 检查动态逻辑是否正确
        const cntr = await queryRunner.manager.createQueryBuilder<Cntr>(Cntr, 'cntr_file')
            .where('id = :cntr_no', { cntr_no: dto.cntr_no })
            .getOne();
        if (isNil(cntr)) {
            throw new Error(`箱号${dto.cntr}没有箱根，创建动态失败`)
        }
        // 最后一条动态
        const lastCntrStat = await queryRunner.manager.createQueryBuilder<PortCntrStat>(PortCntrStat, 'port_cntr_stat')
            .select(['stat_tim', 'cntr_stat_cod'])
            .where('cntr_no = :cntr_no', { cntr_no: dto.cntr_no })
            .andWhere((qb) => {
                const subQuery = qb.subQuery().select('max(stat_tim) as last_tim')
                    .from(PortCntrStat, 'port_cntr_stat')
                    .where('cntr_no = :cntr_no', { cntr_no: dto.cntr_no })
                    .getQuery();
                return 'port_cntr_stat.stat_tim = ' + subQuery;
            })
            .getRawOne();
        const lastStat = (isNil(lastCntrStat) ? UUSTAR : lastCntrStat.cntr_stat_cod);
        if (!isNil(lastCntrStat)) {
            if (new Date(dto.stat_tim).getTime() <= new Date(lastCntrStat.stat_tim).getTime()) {
                throw new Error('当前动态时间不能早于上一个动态时间');
            }
        }

        // 检查当前动态是否合法
        const statlogic = await queryRunner.manager.createQueryBuilder<CntrStatLogic>(CntrStatLogic, 'cntr_stat_logic')
            .leftJoinAndSelect('cntr_stat_logic.nextCntrStat', 'c_cntr_stat')
            .where('cntr_stat_logic.stat_cod = :stat_cod', { stat_cod: lastStat })
            .andWhere('cntr_stat_logic.next_stat_cod = :next_stat_cod', { next_stat_cod: dto.cntr_stat_cod })
            .getOne();


        if (isNil(statlogic)) {
            throw new Error(`动态逻辑错误失败`)
        }
        dto.current_stat = statlogic.nextCntrStat.current_stat;
        dto.cal_stack = statlogic.nextCntrStat.cal_stack;
        // insertCntrResult.raw.insertId
        const insertCntrResult = await queryRunner.manager.createQueryBuilder<PortCntrStat>(PortCntrStat, 'port_cntr_stat')
            .insert()
            .into(PortCntrStat)
            .values(dto)
            .execute();
        // console.log(insertCntrResult.raw.insertId, dto.cntr, dto.cntr_stat_cod);

        await queryRunner.manager.createQueryBuilder<Cntr>(Cntr, 'cntr_file')
            .update(Cntr)
            .set({
                current_stat: statlogic.nextCntrStat.current_stat,
                cfs_cod: dto.cfs_cod
            })
            .where('id = :cntr_no', { cntr_no: dto.cntr_no })
            .execute();
    }
    async delPortCntrStat(dto: DeletePortCntrStatsDto): Promise<ResponseJson> {
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            for (let index = 0; index < dto.cntrStats.length; index++) {
                const element = dto.cntrStats[index];
                await this.deletePortCntrStat(element, queryRunner);
            }
            await queryRunner.commitTransaction();
            return {
                code: ResponseCode.SuccessWithInfo,
                msg: ['删除成功']
            }
        } catch (err) {
            await queryRunner.rollbackTransaction();
            throw err;
        } finally {
            await queryRunner.release();
        }

    }
    /**
     * 删动态
     * @param dto 
     * @param queryRunner 
     */
    async deletePortCntrStat(dto: DeletePortCntrStatDto, queryRunner: QueryRunner) {
        const errors = await validate(plainToInstance(DeletePortCntrStatDto, dto));
        if (errors.length > 0) {
            throw new Error(Object.values(errors[0].constraints).join('   '));
        }

        // 动态列表
        const cntrStats = await queryRunner.manager.createQueryBuilder<PortCntrStat>(PortCntrStat, 'port_cntr_stat')
            .where('cntr_no = :cntr_no', { cntr_no: dto.cntr_no })
            .orderBy('stat_tim', 'DESC')
            .getMany();
        if (cntrStats.length === 0) {
            throw new Error('动态异常');
        }
        if (cntrStats[0].id !== dto.id) {
            throw new Error('不是最后一个动态，不能删除');
        }
        /* const lastCntrStat = await queryRunner.manager.createQueryBuilder<PortCntrStat>(PortCntrStat, 'port_cntr_stat')
            .where('cntr_no = :cntr_no', { cntr_no: dto.cntr_no })
            .andWhere((qb) => {
                const subQuery = qb.subQuery().select('max(stat_tim) as last_tim')
                    .from(PortCntrStat, 'port_cntr_stat')
                    .where('cntr_no = :cntr_no', { cntr_no: dto.cntr_no })
                    .getQuery();
                return 'port_cntr_stat.stat_tim = ' + subQuery;
            })
            .getOne(); */
        const cntr = await queryRunner.manager.createQueryBuilder<Cntr>(Cntr, 'cntr_file')
            .where('id = :cntr_no', { cntr_no: cntrStats[0].cntr_no })
            .getOne();

        const findPlan = await queryRunner.manager.createQueryBuilder<CntrPlan>(CntrPlan, 'truck_cntr_plan')
            .where('id = :plan_id', { plan_id: cntrStats[0].plan_id })
            .getOne();
        // 动态特殊处理
        if (cntrStats[0].cntr_stat_cod === EITZJC) {// 退租进场
            await queryRunner.manager.createQueryBuilder()
                .update(CntrLease)
                .set({
                    rent_sn: null,
                    back_date: null,
                    back_truck_no_c: null,
                    back_driver_tel: null
                })
                .where('cntr_no = :cntr_no', { cntr_no: cntrStats[0].cntr_no })
                .andWhere('rent_sn = :plan_id', { plan_id: cntrStats[0].plan_id })
                .andWhere('back_date = :back_date', { back_date: cntrStats[0].stat_tim })
                .execute();
        }
        if (cntrStats[0].cntr_stat_cod === EOQZCC) { // 起租出场
            // cntr_lease
            await queryRunner.manager.createQueryBuilder()
                .delete()
                .from(CntrLease)
                /* .set({
                    lease_sn: null,
                    out_date: null,
                    out_truck_no_c: null,
                    out_driver_tel: null
                }) */
                .where('cntr_no = :cntr_no', { cntr_no: cntrStats[0].cntr_no })
                .andWhere('lease_sn = :plan_id', { plan_id: cntrStats[0].plan_id })
                .andWhere('out_date = :out_date', { out_date: cntrStats[0].stat_tim })
                .execute();
        }
        // 动态统一处理
        if (cntrStats.length === 1) {  // 只有一个动态
            await queryRunner.manager.createQueryBuilder()
                .delete()
                .from(FeeDet)
                .where('cntr_no = :cntr_no', { cntr_no: cntrStats[0].cntr_no })
                .execute();
            await queryRunner.manager.createQueryBuilder()
                .delete()
                .from(CntrLease)
                .where('cntr_no = :cntr_no', { cntr_no: cntrStats[0].cntr_no })
                .execute();
            await queryRunner.manager.createQueryBuilder()
                .delete()
                .from(Cntr)
                .where('id = :cntr_no', { cntr_no: cntrStats[0].cntr_no })
                .execute();
            await queryRunner.manager.createQueryBuilder()
                .delete()
                .from(CntrPlanCntr)
                .where('cntr_no = :cntr_no', { cntr_no: cntrStats[0].cntr_no })
                .execute();
        } else {
            // fee_det
            // TODO:: 删除动态时堆存费、租赁费还未处理
            await queryRunner.manager.createQueryBuilder()
                .delete()
                .from(FeeDet)
                .where('cntr_no = :cntr_no', { cntr_no: cntrStats[0].cntr_no })
                .andWhere('fee_tim = :fee_tim', { fee_tim: cntrStats[0].stat_tim })
                .execute();

            // cntr_file
            await queryRunner.manager.createQueryBuilder()
                .update(Cntr)
                .set({
                    current_stat: cntrStats[1].current_stat,
                    cfs_cod: cntrStats[1].cfs_cod
                })
                .where('id = :cntr_no', { cntr_no: cntrStats[1].cntr_no })
                .execute();
            // truck_cntr_plan_cntr
            await queryRunner.manager.createQueryBuilder()
                .update(CntrPlanCntr)
                .set({
                    used_id: 'N'
                })
                .where('truck_sn = :plan_id', { plan_id: cntrStats[0].plan_id })
                .andWhere('cntr = :cntr', { cntr: cntrStats[0].cntr })
                .execute();
        }


        // truck_cntr_plan
        if (findPlan) {
            await queryRunner.manager.createQueryBuilder()
                .update(CntrPlan)
                .set({
                    cntr_work_num: findPlan.cntr_work_num - 1,
                    finish_id: 'N',
                })
                .where('id = :plan_id', { plan_id: cntrStats[0].plan_id })
                .execute();
        }

        // 删除动态
        await queryRunner.manager.createQueryBuilder()
            .delete()
            .from(PortCntrStat)
            .where('id = :id', { id: dto.id })
            .execute();
    }

    async queryPortCntrStat(
        query: SqlQueryDto,
    ): Promise<ResponseJson<PortCntrStat[], number>> {
        return await this.crud.query<PortCntrStat>(PortCntrStat, query);
    }

    /**
     * 创建Cnr、PortCntrStat
     * @param queryRunner
     * @param cntrObj
     * @returns
     */
    async createCntr(
        queryRunner: QueryRunner,
        cntrObj: CreateCntrDto,
    ): Promise<number> {

        const errors = await validate(plainToInstance(CreateCntrDto, cntrObj));
        if (errors.length > 0) {
            throw new Error(Object.values(errors[0].constraints).join('   '));
        }
        const count = await queryRunner.manager
            .createQueryBuilder<Cntr>(Cntr, 'cntr_file')
            .where('current_stat <> :stat and cntr = :cntr', {
                stat: '0',
                cntr: cntrObj.cntr,
            })
            .getCount();
        if (count > 0) {
            throw new Error('箱号' + cntrObj.cntr + '已在场');
        }

        const insertCntrResult = await queryRunner.manager
            .createQueryBuilder()
            .insert()
            .into(Cntr)
            .values(cntrObj)
            .execute();

        if (insertCntrResult.raw.affectedRows === 1) {
            // 写入port_cntr_stat
            /* const cntrStat = {
                cntr_no: insertCntrResult.raw.insertId,
                cntr_stat_cod: cntrObj.last_cntr_stat,
                stat_tim: cntrObj.last_stat_tim,
                in_cy_id: cntrObj.current_stat,
                dam_id: cntrObj.dam_id,
                cntr_siz_cod: cntrObj.cntr_siz_cod,
                cntr_typ_cod: cntrObj.cntr_typ_cod,
                send_id: 'N',
                cntr: cntrObj.cntr,
                bill_no: cntrObj.bill_no,
            };
            await queryRunner.manager
                .createQueryBuilder()
                .insert()
                .into(PortCntrStat)
                .values(cntrStat)
                .execute(); */
            return insertCntrResult.raw.insertId;
        } else {
            return 0;
        }
    }
    async checkCntrUpdate(
        dto: UpdateCntrDto | number,
        q?: QueryRunner,
    ): Promise<boolean> {
        const queryRunner = q ?? this.datasource.createQueryRunner();
        const cntrID = typeof dto === 'number' ? dto : dto.id;
        const cntr = await queryRunner.manager
            .createQueryBuilder<Cntr>(Cntr, 'cntr_file')
            .select()
            .where('id = :id', { id: cntrID })
            .getOne();

        if (cntr.current_stat === '0') {
            throw new Error(`箱号${cntr.cntr}已离场不能修改`);
        }
        const feedets = await queryRunner.manager
            .createQueryBuilder<FeeDet>(FeeDet, 'fee_det')
            .select()
            .where(
                "cntr_no = :cntr_no and (audit_id = 'Y' or audit_finish_id = 'Y') ",
                { cntr_no: cntrID },
            )
            .getMany();

        if (feedets.length > 0) {
            throw new Error(`箱号${cntr.cntr}已生成核销费用不能修改`);
            /* return {
                code: ResponseCode.FailWithMsg,
                msg: [`箱号${cntr.cntr}已生成核销费用不能修改`]
            }; */
        }
        return true;
    }
    async updateCntr(
        dto: UpdateCntrDto,
        q?: QueryRunner,
    ): Promise<ResponseJson<Cntr, null>> {
        const queryRunner = q ?? this.datasource.createQueryRunner();
        const errors = await validate(plainToInstance(UpdateCntrDto, dto));
        if (errors.length > 0) {
            throw new Error(Object.values(errors[0].constraints).join('   '));
        }
        await this.checkCntrUpdate(dto, queryRunner);

        const updateCntrResult = await queryRunner.manager
            .createQueryBuilder()
            .update(Cntr)
            .set(
                omit(dto, [
                    'id',
                    'stack_days',
                    'fee0101currency',
                    'fee0101amount',
                    'fee0103currency',
                    'fee0103amount',
                    'in_plan_name',
                    'out_plan_name'
                ]),
            )
            .where('id = :id', { id: dto.id })
            .execute();
        if (updateCntrResult.affected != 1) {
            throw new Error(`箱号${dto.cntr}修改失败`);
        }
        const cntr = await this.datasource
            .createQueryBuilder<Cntr>(Cntr, 'cntr_file')
            .where('id = :id', { id: dto.id })
            .getOne();

        return {
            code: ResponseCode.SuccessWithInfo,
            msg: ['修改成功'],
            obj: cntr,
        };
    }
    async deleteCntr(dto: DeleteDto): Promise<ResponseJson> {
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            for (let index = 0; index < dto.ids.length; index++) {
                const element = dto.ids[index];
                await this.checkCntrUpdate(element, queryRunner);
            }
            const r = await this.crud.delete<Cntr>(Cntr, dto, queryRunner);
            await queryRunner.commitTransaction();
            return r;
        } catch (err) {
            await queryRunner.rollbackTransaction();

            throw err;
        } finally {
            await queryRunner.release();
        }
    }

    async ReplyCntrFix(dto: ReplyCntrFixDto, oper_nam: string): Promise<ResponseJson> {
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            const sysdate = new Date();
            const mails: {
                cfs: number,
                cfsEmail: string,
                msg: string
            }[] = [];
            const reply_msg = dto.reply_id === 'R' ? '批复' : '退回';
            for (let index = 0; index < dto.ids.length; index++) {
                const element = dto.ids[index];
                const cntr = await queryRunner.manager.createQueryBuilder<Cntr>(Cntr, 'cntr_file')
                    .select(['repair_reply_id', 'cntr', 'cntr_owner', 'cfs_cod', 'bill_no'])
                    .where('id = :id', { id: element })
                    .getRawOne();
                if (cntr.repair_reply_id === 'S') {
                    if (dto.reply_id !== 'R') {
                        throw new Error(`箱号${cntr.cntr}修箱报价、批复状态错误`);
                    }
                }
                if (cntr.repair_reply_id === 'R') {
                    if (dto.reply_id !== 'E') {
                        throw new Error(`箱号${cntr.cntr}修箱报价、批复状态错误`);
                    }
                }
                if (cntr.repair_reply_id === 'E') {
                    throw new Error(`箱号${cntr.cntr}修箱报价、批复状态错误`);
                }
                /* if (cntr.repair_reply_id !== 'S') {
                    throw new Error(`箱号${cntr.cntr}未提交报价，不能批复`);
                } */
                await queryRunner.manager.createQueryBuilder()
                    .insert()
                    .into(FixReply)
                    .values({
                        cntr_no: element,
                        msg_time: sysdate,
                        oper_name: oper_nam,
                        reply_id: dto.reply_id,
                        msg: dto.msg
                    })
                    .execute();
                await queryRunner.manager.createQueryBuilder()
                    .update(Cntr)
                    .set({
                        repair_reply_id: dto.reply_id,
                        repair_tim: sysdate
                    })
                    .where('id = :id', { id: element })
                    .execute();
                if (dto.reply_id === 'R') { // 批复
                    // 检查此箱号是否存在修箱费
                    const feecheck = await queryRunner.manager.createQueryBuilder<FeeDet>(FeeDet, 'fee_det')
                        .select('count(1)', 'count')
                        .where('cntr_no = :cntr_no', { cntr_no: element })
                        .andWhere("fee_cod = '0106'")
                        .andWhere("fee_in_out = 'O'")
                        .getRawOne();
                    if (feecheck.count > 0) {
                        throw new Error(`箱号${cntr.cntr}已存在应付修箱费，批复失败`);
                    }
                    const total_money = await this.datasource.manager.createQueryBuilder<CntrFixItem>(CntrFixItem, 'fix_list')
                        .select('sum(total_money) as total_money')
                        .where('cntr_no = :cntr_no', { cntr_no: element })
                        .getRawOne();
                    const feedetObj = {} as unknown as FeedetDto;
                    feedetObj.cntr_no = element;
                    feedetObj.fee_owner = cntr.cntr_owner;
                    feedetObj.fee_typ_cod = '03';
                    feedetObj.fee_cod = '0106';
                    feedetObj.fee_unit_cod = cntr.cfs_cod;
                    feedetObj.amount = total_money.total_money;
                    feedetObj.currency_cod = 'RMB';
                    feedetObj.fee_nam = oper_nam;
                    feedetObj.fee_tim = sysdate;
                    feedetObj.fee_in_out = 'O';
                    feedetObj.bill_no = cntr.bill_no;
                    await this.businessService.createFeedet(
                        feedetObj,
                        queryRunner,
                    );
                } else { // 退回
                    await queryRunner.manager.createQueryBuilder()
                        .delete()
                        .from(FeeDet)
                        .where('cntr_no = :cntr_no', { cntr_no: element })
                        .andWhere("fee_cod = '0106'")
                        .andWhere("fee_in_out = 'O'")
                        .execute();
                }
                const findMailIndex = mails.findIndex((m) => m.cfs === cntr.cfs_cod);

                if (findMailIndex > -1) {
                    mails[findMailIndex].msg += ` ${cntr.cntr}`
                } else {
                    const cfsmail = await this.datasource.createQueryBuilder()
                        .select(['email', 'cfs_nam_c'])
                        .from(CyCfs, 'c_cy_cfs')
                        .where('id = :id', { id: cntr.cfs_cod })
                        .getRawOne();
                    mails.push({
                        cfs: cntr.cfs_cod,
                        cfsEmail: cfsmail['email'],
                        msg: `修箱报价${reply_msg} 批复内容${dto.msg}
                              箱号：${cntr.cntr}`
                    })
                }
            }
            await queryRunner.commitTransaction();
            const mailOption: EmailType = {};
            for (let index = 0; index < mails.length; index++) {
                const element = mails[index];
                mailOption.to = `${element.cfsEmail}`;
                mailOption.subject = '修箱报价批复';
                mailOption.text = element.msg;
                const sendR = await this.sharedService.sendMail(mailOption);
            }
            return {
                code: ResponseCode.SuccessWithInfo,
                msg: ['批复成功']
            }
        } catch (err) {
            await queryRunner.rollbackTransaction();
            throw err;
        } finally {
            await queryRunner.release();
        }
    }

    async queryFixQuotation(query: SqlQueryDto): Promise<ResponseJson<VFixQuotation[], number>> {
        return await this.crud.query<VFixQuotation>(VFixQuotation, query);
    }


    async insertInplan<T extends InPlanDto | PlanDto>(dto: T, cls: new () => T, queryRunner: QueryRunner): Promise<number> {
        const fees = dto.fees;
        const plan = omit(dto, ['fees']);
        plan.carry_id = 'A';

        plan.finish_id = 'N';

        const errors = await validate(plainToInstance(cls, plan));
        if (errors.length > 0) {
            throw new Error(Object.values(errors[0].constraints).join('   '));
        }
        const insertPlanResult = await queryRunner.manager
            .createQueryBuilder()
            .insert()
            .into(CntrInPlan)
            .values(plan)
            .execute();
        const plan_id = insertPlanResult.identifiers[0]['id'];

        if (!isNil(fees)) {
            fees.forEach((fee) => {
                fee.truck_sn = plan_id;
            });

            await queryRunner.manager
                .createQueryBuilder()
                .insert()
                .into(CntrPlanFee)
                .values(fees)
                .execute();
        }
        return plan_id;
    }

    async createInPlan<T extends InPlanDto | PlanDto>(
        dto: T,
        cls: new () => T
        // q?: QueryRunner,
    ): Promise<ResponseJson<CntrInPlan, EmailResultType>> {
        const errors = await validate(plainToInstance(cls, dto));
        if (errors.length > 0) {
            throw new Error(Object.values(errors[0].constraints).join('   '));
        }
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            const emailResult: EmailResultType = {
                status: '成功'
            }
            let plan_id: number;
            if (dto instanceof InPlanDto) {
                plan_id = await this.insertInplan(dto, InPlanDto, queryRunner);
            } else {
                plan_id = await this.insertInplan(dto, PlanDto, queryRunner);
            }

            await queryRunner.commitTransaction();
            const newPlan = await this.datasource
                .createQueryBuilder<CntrInPlan>(
                    CntrInPlan,
                    'v_truck_cntr_inplan',
                )
                .leftJoinAndSelect(
                    'v_truck_cntr_inplan.fees',
                    'truck_cntr_plan_fee',
                )
                .where('v_truck_cntr_inplan.id = :id', { id: plan_id })
                .getOne();

            // 发送邮件  场站、供应商
            if (newPlan.need_email === 'Y') {
                const mailOption: EmailType = {};
                /* const supplymail = await this.datasource.createQueryBuilder()
                    .select(['email', 'client_nam_c'])
                    .from(Client, 'c_client_cod')
                    .where('id = :id', { id: newPlan.cntr_oper_cod })
                    .getRawOne(); */
                const cfsmail = await this.datasource.createQueryBuilder()
                    .select(['email', 'cfs_nam_c'])
                    .from(CyCfs, 'c_cy_cfs')
                    .where('id = :id', { id: newPlan.cfs_cod })
                    .getRawOne();

                if (/* supplymail?.email && */ cfsmail?.email) {
                    mailOption.to = `${cfsmail['email']}`;
                    mailOption.subject = '新增进场计划';
                    /* mailOption.text = `
                        进场计划号：${newPlan.id}
                        尺寸： ${newPlan.cntr_siz_cod}
                        箱型： ${newPlan.cntr_typ_cod}
                        箱量： ${newPlan.cntr_num}
                        堆场： ${cfsmail.cfs_nam_c}
                    `; */
                    mailOption.html = `
                        <p>进场计划号：${newPlan.id}</p>
                        <p>尺寸： ${newPlan.cntr_siz_cod}</p>
                        <p>箱型： ${newPlan.cntr_typ_cod}</p>
                        <p>箱量： ${newPlan.cntr_num}</p>
                        <p>堆场： ${cfsmail.cfs_nam_c}</p>
                        <br>
                        <br>
                        <br>
                        <br>
                        <p><span style="color: red; font-weight: bold;">此邮件由系统自动发送，无需回复，还请您知悉。</span></p>
                        <br>
                        <br>
                        <p>Thanks & Best Regards</p>
                        <p>*********************</p>
                        <p>Yizhou Container Co., Limited</p>
                        <p>Tel: +86 532-8098 1257</p>
                        <p>Add: NO.877, The west of Lijiang Road, Huang dao District, Qingdao, shangdong, china</p>
                    `;
                    const sendR = await this.sharedService.sendMail(mailOption);
                    if (sendR !== 'SUCCESS') {
                        emailResult.status = '失败';
                        emailResult.msg = sendR;
                    }
                } else {
                    emailResult.status = '失败';
                    emailResult.msg = '邮件发送失败，场站邮件地址错误';
                }
            }
            return {
                code: ResponseCode.SuccessWithInfo,
                obj: newPlan,
                msg: ['创建成功'],
                ext: emailResult,
            };
        } catch (err) {
            await queryRunner.rollbackTransaction();
            throw err;
        } finally {
            await queryRunner.release();
        }
    }
    /**
     * 根据新的truck_cntr_plan_fee，修改已存在的fee_det. 同时处理增、删、改
     * @param oldPlanFeeIttem 
     * @param q 
     */
    async changeFeedetCausedInPlanUpdate(oldPlanFeeItems: CntrPlanFee[], newPlanFeeItems: CntrPlanFee[], planID: number, bill_no: string, oper_nam: string, q?: QueryRunner) {

        const queryRunner = q ?? this.datasource.createQueryRunner();



        /* const newPlanFeeItems = await queryRunner.manager.createQueryBuilder<CntrPlanFee>(CntrPlanFee, 'truck_cntr_plan_fee')
        .where('truck_sn = :truck_sn', { truck_sn: planID })
        .getMany(); */


        // 两数组去重  未发生变动的费用项目不用处理
        for (let index = oldPlanFeeItems.length - 1; index >= 0; index--) {
            const oldItem = oldPlanFeeItems[index];
            for (let index_1 = newPlanFeeItems.length - 1; index_1 >= 0; index_1--) {
                const newItem = newPlanFeeItems[index_1];
                if (oldItem.id === newItem.id && oldItem.fee_owner === newItem.fee_owner && oldItem.fee_in_out === newItem.fee_in_out
                    && oldItem.fee_unit_cod === newItem.fee_unit_cod && oldItem.fee_typ_cod === newItem.fee_typ_cod
                    && oldItem.fee_cod === newItem.fee_cod && oldItem.currency_cod === newItem.currency_cod
                    && oldItem.amount === newItem.amount
                ) {
                    newPlanFeeItems.splice(index_1, 1);
                    oldPlanFeeItems.splice(index, 1);
                    break;
                }
            }
        }
        const cntrs = await queryRunner.manager.createQueryBuilder<PortCntrStat>(PortCntrStat, 'port_cntr_stat')
            .select(['cntr_no', 'stat_tim'])
            .where('plan_id = :plan_id', { plan_id: planID })
            .getRawMany();

        /* const cntrs = await queryRunner.manager.createQueryBuilder<Cntr>(Cntr, 'cntr_file')
            .select(['id as id', 'to_port_tim as to_port_tim'])
            .where('in_truck_sn = :in_truck_sn', { in_truck_sn: planID })
            .getRawMany(); */
        // 经处理后的旧数组中删除
        for (let index = 0; index < oldPlanFeeItems.length; index++) {
            const element = oldPlanFeeItems[index];
            for (let index_1 = 0; index_1 < cntrs.length; index_1++) {
                const cntrID = cntrs[index_1]['cntr_no'];
                await queryRunner.manager.createQueryBuilder()
                    .delete()
                    .from(FeeDet)
                    .where('cntr_no = :cntr_no', { cntr_no: cntrID })
                    .andWhere('fee_tim = :fee_tim', { fee_tim: cntrs[index_1]['stat_tim'] })
                    .andWhere('fee_owner = :fee_owner', { fee_owner: element.fee_owner })
                    .andWhere('fee_in_out = :fee_in_out', { fee_in_out: element.fee_in_out })
                    .andWhere('fee_unit_cod = :fee_unit_cod', { fee_unit_cod: element.fee_unit_cod })
                    .andWhere('fee_typ_cod = :fee_typ_cod', { fee_typ_cod: element.fee_typ_cod })
                    .andWhere('fee_cod = :fee_cod', { fee_cod: element.fee_cod })
                    .andWhere('currency_cod = :currency_cod', { currency_cod: element.currency_cod })
                    .andWhere('amount = :amount', { amount: element.amount })
                    .execute();
            }
        }
        // 经处理后的新数组中的  先删除再增加
        for (let index = 0; index < newPlanFeeItems.length; index++) {
            const element = newPlanFeeItems[index];
            // 删除
            for (let index_1 = 0; index_1 < cntrs.length; index_1++) {
                const cntrID = cntrs[index_1]['cntr_no'];
                await queryRunner.manager.createQueryBuilder()
                    .delete()
                    .from(FeeDet)
                    .where('cntr_no = :cntr_no', { cntr_no: cntrID })
                    .andWhere('fee_tim = :fee_tim', { fee_tim: cntrs[index_1]['stat_tim'] })
                    .andWhere('fee_owner = :fee_owner', { fee_owner: element.fee_owner })
                    .andWhere('fee_in_out = :fee_in_out', { fee_in_out: element.fee_in_out })
                    .andWhere('fee_unit_cod = :fee_unit_cod', { fee_unit_cod: element.fee_unit_cod })
                    .andWhere('fee_typ_cod = :fee_typ_cod', { fee_typ_cod: element.fee_typ_cod })
                    .andWhere('fee_cod = :fee_cod', { fee_cod: element.fee_cod })
                    .andWhere('currency_cod = :currency_cod', { currency_cod: element.currency_cod })
                    .andWhere('amount = :amount', { amount: element.amount })
                    .execute();
            }
            // 新增
            for (let index_2 = 0; index_2 < cntrs.length; index_2++) {
                const cntr = cntrs[index_2];
                const feedetObj = {} as unknown as FeedetDto;
                feedetObj.cntr_no = cntr.cntr_no;
                feedetObj.fee_owner = element.fee_owner;
                feedetObj.fee_typ_cod = element.fee_typ_cod;
                feedetObj.fee_cod = element.fee_cod;
                feedetObj.fee_unit_cod = element.fee_unit_cod;
                feedetObj.amount = element.amount;
                feedetObj.currency_cod = element.currency_cod;
                feedetObj.fee_nam = oper_nam;
                feedetObj.fee_tim = cntr.stat_tim;
                feedetObj.fee_in_out = element.fee_in_out;
                feedetObj.bill_no = bill_no;
                await this.businessService.createFeedet(
                    feedetObj,
                    queryRunner,
                );
            }
        }

    }
    async updateInPlan(
        dto: UpdateInPlanDto,
        oper_nam: string
    ): Promise<ResponseJson<CntrInPlan, EmailResultType>> {

        const fees = dto.fees as unknown[] as UpdatePlanFeeDto[];   // 新计划费用
        const plan = omit(dto, ['fees', 'updateFeeFlag', 'cntr_work_num']) as CntrInPlan;
        await checkDto(UpdateInPlanDto, dto);
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();   // 读未提交数据
        try {
            const emailResult: EmailResultType = {
                status: '成功'
            }
            // 检查是否已存在进场计划箱
            const existCntrs = await queryRunner.manager.createQueryBuilder<Cntr>(Cntr, 'cntr_file')
                .select('count(*) as count')
                .where('in_truck_sn = :plan_id', { plan_id: dto.id })
                .getRawOne();
            if (existCntrs.count > 0) {
                // 2024-11-12 coco通知允许修改供应商、尺寸、箱型
                removeProperty(plan, ['stat_cod', /*'cntr_oper_cod', 'cntr_siz_cod', 'cntr_typ_cod',*/
                    'cntr_owner', 'cfs_cod', 'bill_no']);
            }

            // 修改计划信息
            const dbPlan = await queryRunner.manager.createQueryBuilder<CntrPlan>(CntrPlan, 'truck_cntr_plan')
                .select(['cntr_work_num as cntr_work_num', 'cntr_oper_cod as cntr_oper_cod', 'cntr_siz_cod as cntr_siz_cod',
                    'cntr_typ_cod as cntr_typ_cod'
                ],)
                .where('id = :id', { id: dto['id'] })
                .getRawOne();
            plan.finish_id = (plan.cntr_num - (dbPlan?.cntr_work_num ?? 0) > 0) ? 'N' : 'Y';

            const updatePlanResult = await queryRunner.manager
                .createQueryBuilder()
                .update(CntrInPlan)
                .set(plan)
                .where('id = :id', { id: dto['id'] })
                .execute();
            if (updatePlanResult.affected != 1) {
                throw new Error('无此计划，修改失败');
            }
            // 箱进场计划，修改已进场箱信息
            if (dto.stat_cod === EIDXJC && existCntrs.count > 0) {
                const updCntrObj = {};

                if (plan.cntr_oper_cod !== dbPlan.cntr_oper_cod) {
                    Reflect.set(updCntrObj, 'cntr_oper_cod', plan.cntr_oper_cod);
                }
                if (plan.cntr_siz_cod !== dbPlan.cntr_siz_cod) {
                    Reflect.set(updCntrObj, 'cntr_siz_cod', plan.cntr_siz_cod);
                }
                if (plan.cntr_typ_cod !== dbPlan.cntr_typ_cod) {
                    Reflect.set(updCntrObj, 'cntr_typ_cod', plan.cntr_typ_cod);
                }

                if (Reflect.ownKeys(updCntrObj).length > 0) {
                    await queryRunner.manager
                        .createQueryBuilder()
                        .update(Cntr)
                        .set(updCntrObj)
                        .where('in_truck_sn = :plan_id', { plan_id: dto.id })
                        .execute();
                }
            }
            const oldplanfees = await queryRunner.manager.createQueryBuilder<CntrPlanFee>(CntrPlanFee, 'truck_cntr_plan_fee')
                .where('truck_sn = :truck_sn', { truck_sn: dto['id'] })
                .getMany();

            /* await queryRunner.manager.createQueryBuilder().delete().from(CntrPlanFee)
                .where('truck_sn = :id', { id: dto['id'] }).execute(); */
            // 处理 truck_cntr_plan_fee
            if (!isNil(fees)) {
                const feeIds = []; // 已存在需保留的费用
                fees.forEach((fee) => {
                    fee.truck_sn = dto['id'];
                    if (!isNil(fee['id'])) {
                        feeIds.push(fee.id);
                    }
                });

                // 删除不需要的费用
                if (feeIds.length > 0) {
                    await queryRunner.manager
                        .createQueryBuilder()
                        .delete()
                        .from(CntrPlanFee)
                        .where('truck_sn = :truck_sn', { truck_sn: dto['id'] })
                        .andWhere('id not in (:...id)', { id: feeIds })
                        .execute();
                } else {
                    await queryRunner.manager
                        .createQueryBuilder()
                        .delete()
                        .from(CntrPlanFee)
                        .where('truck_sn = :truck_sn', { truck_sn: dto['id'] })
                        .execute();
                }

                fees.forEach(async (element) => {
                    if (isNil(element['id'])) { // 计划上新增的费用
                        const ri = await queryRunner.manager
                            .createQueryBuilder()
                            .insert()
                            .into(CntrPlanFee)
                            .values(element)
                            .execute();
                        element.id = ri.identifiers[0].id;
                    } else { // 原有费用
                        const updateObj = omit(element, ['id', 'isEdit']);
                        const r = await queryRunner.manager
                            .createQueryBuilder()
                            .update(CntrPlanFee)
                            .set(updateObj)
                            .where('id = :id', { id: element.id })
                            .execute();
                    }
                });
            }


            // 处理fee_det 
            if (dto.updateFeeFlag) {
                await this.changeFeedetCausedInPlanUpdate(oldplanfees, fees as CntrPlanFee[], dto['id'], plan.bill_no, oper_nam, queryRunner);
            }
            await queryRunner.commitTransaction();
            const newPlan = await this.datasource
                .createQueryBuilder<CntrInPlan>(
                    CntrInPlan,
                    'v_truck_cntr_inplan',
                )
                .leftJoinAndSelect(
                    'v_truck_cntr_inplan.fees',
                    'truck_cntr_plan_fee',
                )
                .where('v_truck_cntr_inplan.id = :id', { id: dto['id'] })
                .getOne();
            // 发送邮件  场站、供应商
            if (newPlan.need_email === 'Y') {
                const mailOption: EmailType = {};
                /* const supplymail = await this.datasource.createQueryBuilder()
                    .select(['email', 'client_nam_c'])
                    .from(Client, 'c_client_cod')
                    .where('id = :id', { id: newPlan.cntr_oper_cod })
                    .getRawOne(); */
                const cfsmail = await this.datasource.createQueryBuilder()
                    .select(['email', 'cfs_nam_c'])
                    .from(CyCfs, 'c_cy_cfs')
                    .where('id = :id', { id: newPlan.cfs_cod })
                    .getRawOne();

                if (/* supplymail?.email && */ cfsmail?.email) {
                    mailOption.to = `${cfsmail['email']}`;
                    mailOption.subject = '更新进场计划';
                    /* mailOption.text = `
                        进场计划号：${newPlan.id}
                        尺寸： ${newPlan.cntr_siz_cod}
                        箱型： ${newPlan.cntr_typ_cod}
                        箱量： ${newPlan.cntr_num}
                        堆场： ${cfsmail.cfs_nam_c}
                    `; */
                    mailOption.html = `
                        <p>进场计划号：${newPlan.id}</p>
                        <p>尺寸： ${newPlan.cntr_siz_cod}</p>
                        <p>箱型： ${newPlan.cntr_typ_cod}</p>
                        <p>箱量： ${newPlan.cntr_num}</p>
                        <p>堆场： ${cfsmail.cfs_nam_c}</p>
                        <br>
                        <br>
                        <br>
                        <br>
                        <p><span style="color: red; font-weight: bold;">此邮件由系统自动发送，无需回复，还请您知悉。</span></p>
                        <br>
                        <br>
                        <p>Thanks & Best Regards</p>
                        <p>*********************</p>
                        <p>Yizhou Container Co., Limited</p>
                        <p>Tel: +86 532-8098 1257</p>
                        <p>Add: NO.877, The west of Lijiang Road, Huang dao District, Qingdao, shangdong, china</p>
                    `;
                    const sendR = await this.sharedService.sendMail(mailOption);
                    if (sendR !== 'SUCCESS') {
                        emailResult.status = '失败';
                        emailResult.msg = sendR;
                    }
                } else {
                    emailResult.status = '失败';
                    emailResult.msg = '邮件发送失败，供应商或场站邮件地址错误';
                }
            }
            return {
                code: ResponseCode.SuccessWithInfo,
                obj: newPlan,
                msg: ['修改成功'],
                ext: emailResult
            };
        } catch (err) {
            await queryRunner.rollbackTransaction();

            throw err;
        } finally {
            await queryRunner.release();
        }
        // return await this.crud.update<Client>(Client, dto);
    }
    async deleteInCntrPlan(dto: DeleteDto): Promise<ResponseJson> {
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            // 删除预定箱
            await queryRunner.manager.createQueryBuilder()
                .delete()
                .from<CntrPlanCntr>(CntrPlanCntr)
                .where('truck_sn in (:truck_sns)', { truck_sns: dto.ids })
                .execute();
            const cntrs = await queryRunner.manager.createQueryBuilder<Cntr>(Cntr, 'cntr_file')
                .select(['cntr_file.id as id'])
                .where('in_truck_sn in (:...ids)', { ids: dto.ids })
                .getRawMany();
            for (let index = 0; index < cntrs.length; index++) {
                const element = cntrs[index];

                // 删除费用
                await queryRunner.manager.createQueryBuilder()
                    .delete()
                    .from<FeeDet>(FeeDet)
                    .where('cntr_no = :id', { id: element['id'] })
                    .execute();
                // 删除箱子
                await queryRunner.manager.createQueryBuilder()
                    .delete()
                    .from<Cntr>(Cntr)
                    .where('id = :id', { id: element['id'] })
                    .execute();
            }
            const r = await queryRunner.manager.createQueryBuilder()
                .delete()
                .from<CntrInPlan>(CntrInPlan)
                .where('id in (:...ids)', { ids: dto.ids })
                .execute();
            await queryRunner.commitTransaction();
            return {
                code: ResponseCode.SuccessWithSuc,
                msg: [`删除${r.affected}条记录`],
            };

        } catch (err) {
            await queryRunner.rollbackTransaction();
            throw err;
        } finally {
            await queryRunner.release();
        }
    }
    async queryCntrInPlan(
        query: SqlQueryDto,
    ): Promise<ResponseJson<CntrInPlan[], number>> {
        query.left = [
            /*  {
                property: 'truck_cntr_plan.cntrs',
                alias: 'truck_cntr_plan_cntr',
            }, */
            {
                property: 'v_truck_cntr_inplan.fees',
                alias: 'truck_cntr_plan_fee',
            },
        ];
        return await this.crud.query<CntrInPlan>(CntrInPlan, query);
    }
    /**
     * 进场计划预定箱号
     * @param dto 
     * @returns 
     */
    async InPlanAddPreCntrs(dto: InplanImportCntrDto): Promise<ResponseJson<CntrPlanCntr[], null>> {
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            for (let index = 0; index < dto.cntrs.length; index++) {
                const cntr = dto.cntrs[index];
                if (!checkCntrNo(cntr)) {
                    throw new Error(`箱号${cntr}箱号校验失败`);
                }
                const cntrCount = await queryRunner.manager.createQueryBuilder<VCurrentCntr>(VCurrentCntr, 'v_current_cntr_file')
                    .select('count(1)', 'count')
                    .where('cntr = :cntr', { cntr })
                    .getRawOne();
                if (parseInt(cntrCount['count']) > 0) {
                    throw new Error(`箱号${cntr}已在场，不能预定`);
                }
                const plan = await queryRunner.manager.createQueryBuilder<CntrPlanCntr>(CntrPlanCntr, 'truck_cntr_plan_cntr')
                    .select('truck_sn', 'truck_sn')
                    .where('cntr = :cntr', { cntr })
                    .andWhere("used_id = 'N'")
                    .getRawOne();
                if (!isNil(plan?.truck_sn)) {
                    throw new Error(`箱号${cntr}已预定在计划号${plan['truck_sn']}，不能重复预定`);
                }
                await queryRunner.manager.createQueryBuilder()
                    .insert()
                    .into(CntrPlanCntr)
                    .values({
                        truck_sn: dto.plan_id,
                        cntr: cntr
                    })
                    .orIgnore()
                    .execute();

            }
            await queryRunner.commitTransaction();
            const r = await this.datasource.manager.createQueryBuilder<CntrPlanCntr>(CntrPlanCntr, 'truck_cntr_plan_cntr')
                .where('truck_sn = :truck_sn', { truck_sn: dto.plan_id })
                .getMany();
            return {
                code: ResponseCode.SuccessWithInfo,
                msg: ['添加成功'],
                obj: r,
            }
        } catch (err) {
            await queryRunner.rollbackTransaction();
            throw err;
        } finally {
            await queryRunner.release();
        }
    }
    /**
     * 出场计划、起租计划、站间调箱出场计划预定箱号
     * @param dto 
     * @returns 
     */
    async OutPlanAddPreCntrs(dto: InplanImportCntrDto): Promise<ResponseJson<CntrPlanCntr[], null>> {
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            const plan = await queryRunner.manager.createQueryBuilder<CntrPlan>(CntrPlan, 'truck_cntr_plan')
                .select(['cfs_cod', 'cntr_siz_cod', 'cntr_typ_cod', 'cntr_owner', 'dam_id'])
                .where('id = :id', { id: dto.plan_id })
                .getRawOne();


            for (let index = 0; index < dto.cntrs.length; index++) {
                const cntr = dto.cntrs[index].trim();
                /* if (!checkCntrNo(cntr)) {
                    throw new Error(`箱号${cntr}箱号校验失败`);
                } */
                const dbCntr = await queryRunner.manager.createQueryBuilder<Cntr>(Cntr, 'cntr_file')
                    .select(['cfs_cod', 'cntr_siz_cod', 'cntr_typ_cod', 'cntr_owner', 'dam_id', 'id'])
                    .where('cntr = :cntr', { cntr: cntr })
                    .andWhere("current_stat = '2'")
                    // .andWhere('cfs_cod = :cfs_cod', { cfs_cod: plan['cfs_cod'] })
                    .getRawOne();

                if (isNil(dbCntr)) {
                    throw new Error(`箱号${cntr}不在场，不能预定`);
                }
                if (dbCntr.cfs_cod !== plan.cfs_cod) {
                    throw new Error(`箱号${cntr}不在计划堆场，不能预定`);
                }
                if (dbCntr.cntr_siz_cod !== plan.cntr_siz_cod) {
                    throw new Error(`箱号${cntr}尺寸与计划不一致，不能预定`);
                }
                if (dbCntr.cntr_typ_cod !== plan.cntr_typ_cod) {
                    throw new Error(`箱号${cntr}箱型与计划不一致，不能预定`);
                }
                if (dbCntr.cntr_owner !== plan.cntr_owner) {
                    throw new Error(`箱号${cntr}箱主与计划不一致，不能预定`);
                }
                /* if (dbCntr.dam_id !== plan.dam_id) {
                    throw new Error(`箱号${cntr}箱况与计划不一致，不能预定`);
                } */
                const existplan = await queryRunner.manager.createQueryBuilder<CntrPlanCntr>(CntrPlanCntr, 'truck_cntr_plan_cntr')
                    .select('truck_sn', 'truck_sn')
                    .where('cntr = :cntr', { cntr })
                    .andWhere("used_id = 'N'")
                    .getRawOne();


                if (!isNil(existplan?.truck_sn)) {
                    throw new Error(`箱号${cntr}已预定在计划号${existplan['truck_sn']}，不能重复预定`);
                }
                await queryRunner.manager.createQueryBuilder()
                    .insert()
                    .into(CntrPlanCntr)
                    .values({
                        truck_sn: dto.plan_id,
                        cntr: cntr,
                        cntr_no: dbCntr.id
                    })
                    .orIgnore()
                    .execute();
            }
            await queryRunner.commitTransaction();
            const r = await this.datasource.manager.createQueryBuilder<CntrPlanCntr>(CntrPlanCntr, 'truck_cntr_plan_cntr')
                .where('truck_sn = :truck_sn', { truck_sn: dto.plan_id })
                .getMany();
            return {
                code: ResponseCode.SuccessWithInfo,
                msg: ['添加成功'],
                obj: r,
            }
        } catch (err) {
            await queryRunner.rollbackTransaction();
            throw err;
        } finally {
            await queryRunner.release();
        }
    }
    /**
     * 退租计划预定箱号
     * @param dto 
     */
    async RentPlanAddPreCntrs(dto: InplanImportCntrDto): Promise<ResponseJson<CntrPlanCntr[], null>> {
        const queryRunner = this.datasource.createQueryRunner();

        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            const plan = await queryRunner.manager.createQueryBuilder<CntrPlan>(CntrPlan, 'truck_cntr_plan')
                .select(['cntr_siz_cod', 'cntr_typ_cod', 'cntr_owner'])
                .where('id = :id', { id: dto.plan_id })
                .getRawOne();


            for (let index = 0; index < dto.cntrs.length; index++) {
                const cntr = dto.cntrs[index].trim();
                /* if (!checkCntrNo(cntr)) {
                    throw new Error(`箱号${cntr}箱号校验失败`);
                } */
                const dbCntr = await queryRunner.manager.createQueryBuilder<Cntr>(Cntr, 'cntr_file')
                    .select(['cntr_siz_cod', 'cntr_typ_cod', 'cntr_owner', 'id'])
                    .where('cntr = :cntr', { cntr: cntr })
                    .andWhere("current_stat = '1'")   // 出租箱
                    // .andWhere('cfs_cod = :cfs_cod', { cfs_cod: plan['cfs_cod'] })
                    .getRawOne();

                if (isNil(dbCntr)) {
                    throw new Error(`箱号${cntr}不是起租箱，不能预定`);
                }

                if (dbCntr.cntr_siz_cod !== plan.cntr_siz_cod) {
                    throw new Error(`箱号${cntr}尺寸与计划不一致，不能预定`);
                }
                if (dbCntr.cntr_typ_cod !== plan.cntr_typ_cod) {
                    throw new Error(`箱号${cntr}箱型与计划不一致，不能预定`);
                }
                if (dbCntr.cntr_owner !== plan.cntr_owner) {
                    throw new Error(`箱号${cntr}箱主与计划不一致，不能预定`);
                }

                const existplan = await queryRunner.manager.createQueryBuilder<CntrPlanCntr>(CntrPlanCntr, 'truck_cntr_plan_cntr')
                    .select('truck_sn', 'truck_sn')
                    .where('cntr = :cntr', { cntr })
                    .andWhere("used_id = 'N'")
                    .getRawOne();


                if (!isNil(existplan?.truck_sn)) {
                    throw new Error(`箱号${cntr}已预定在计划号${existplan['truck_sn']}，不能重复预定`);
                }
                await queryRunner.manager.createQueryBuilder()
                    .insert()
                    .into(CntrPlanCntr)
                    .values({
                        truck_sn: dto.plan_id,
                        cntr: cntr,
                        cntr_no: dbCntr.id
                    })
                    .orIgnore()
                    .execute();
            }
            await queryRunner.commitTransaction();
            const r = await this.datasource.manager.createQueryBuilder<CntrPlanCntr>(CntrPlanCntr, 'truck_cntr_plan_cntr')
                .where('truck_sn = :truck_sn', { truck_sn: dto.plan_id })
                .getMany();
            return {
                code: ResponseCode.SuccessWithInfo,
                msg: ['添加成功'],
                obj: r,
            }
        } catch (err) {
            await queryRunner.rollbackTransaction();
            throw err;
        } finally {
            await queryRunner.release();
        }
    }
    /**
     * 站间调箱进场计划预定箱号
     * @param dto 
     */
    async TransInPlanAddPreCntrs(dto: InplanImportCntrDto): Promise<ResponseJson<CntrPlanCntr[], null>> {
        const queryRunner = this.datasource.createQueryRunner();

        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            const plan = await queryRunner.manager.createQueryBuilder<CntrPlan>(CntrPlan, 'truck_cntr_plan')
                .select(['cntr_siz_cod', 'cntr_typ_cod', 'cntr_owner'])
                .where('id = :id', { id: dto.plan_id })
                .getRawOne();


            for (let index = 0; index < dto.cntrs.length; index++) {
                const cntr = dto.cntrs[index].trim();
                /* if (!checkCntrNo(cntr)) {
                    throw new Error(`箱号${cntr}箱号校验失败`);
                } */
                const dbCntr = await queryRunner.manager.createQueryBuilder<Cntr>(Cntr, 'cntr_file')
                    .select(['cntr_siz_cod', 'cntr_typ_cod', 'cntr_owner', 'id'])
                    .where('cntr = :cntr', { cntr: cntr })
                    .andWhere("current_stat = '2' || current_stat = '3'")   // 离场箱、在场箱
                    // .andWhere('cfs_cod = :cfs_cod', { cfs_cod: plan['cfs_cod'] })
                    .getRawOne();

                if (isNil(dbCntr)) {
                    throw new Error(`箱号${cntr}不是在场箱或离场箱，不能预定`);
                }

                if (dbCntr.cntr_siz_cod !== plan.cntr_siz_cod) {
                    throw new Error(`箱号${cntr}尺寸与计划不一致，不能预定`);
                }
                if (dbCntr.cntr_typ_cod !== plan.cntr_typ_cod) {
                    throw new Error(`箱号${cntr}箱型与计划不一致，不能预定`);
                }
                if (dbCntr.cntr_owner !== plan.cntr_owner) {
                    throw new Error(`箱号${cntr}箱主与计划不一致，不能预定`);
                }

                const existplan = await queryRunner.manager.createQueryBuilder<CntrPlanCntr>(CntrPlanCntr, 'truck_cntr_plan_cntr')
                    .select('truck_sn', 'truck_sn')
                    .where('cntr = :cntr', { cntr })
                    .andWhere("used_id = 'N'")
                    .getRawOne();


                if (!isNil(existplan?.truck_sn)) {
                    const preStatCod = await queryRunner.manager.createQueryBuilder<CntrPlan>(CntrPlan, 'truck_cntr_plan')
                        .select('stat_cod')
                        .where('id = :plan_id', { plan_id: existplan?.truck_sn })
                        .getRawOne();
                    if (existplan?.truck_sn === dto.plan_id) {
                        throw new Error(`箱号${cntr}已预定在计划号${existplan['truck_sn']}，不能重复预定`);
                    }
                    if (preStatCod.stat_cod !== 'EOZYXC') { // 站间调箱出场
                        throw new Error(`箱号${cntr}已预定在计划号${existplan['truck_sn']}，不能重复预定`);
                    }
                }
                await queryRunner.manager.createQueryBuilder()
                    .insert()
                    .into(CntrPlanCntr)
                    .values({
                        truck_sn: dto.plan_id,
                        cntr: cntr,
                        cntr_no: dbCntr.id
                    })
                    .orIgnore()
                    .execute();
            }
            await queryRunner.commitTransaction();
            const r = await this.datasource.manager.createQueryBuilder<CntrPlanCntr>(CntrPlanCntr, 'truck_cntr_plan_cntr')
                .where('truck_sn = :truck_sn', { truck_sn: dto.plan_id })
                .getMany();
            return {
                code: ResponseCode.SuccessWithInfo,
                msg: ['添加成功'],
                obj: r,
            }
        } catch (err) {
            await queryRunner.rollbackTransaction();
            throw err;
        } finally {
            await queryRunner.release();
        }
    }
    async deletePlanPreCntr(dto: DeleteDto): Promise<ResponseJson> {
        const check = await this.datasource.manager.createQueryBuilder<CntrPlanCntr>(CntrPlanCntr, 'truck_cntr_plan_cntr')
            .select('count(1)', 'count')
            .where('id in (:...ids)', { ids: dto.ids })
            .andWhere("used_id = 'Y'")
            .getRawOne();
        if (parseInt(check['count']) > 0) {
            throw new Error('不能删除已完成预定箱号');
        }
        return await this.crud.delete<CntrPlanCntr>(CntrPlanCntr, dto);
    }

    /**
     * 查询计划预定箱号
     * @param query 
     */
    async queryPlanPreCntr(query: QueryPlanCntrsDto): Promise<ResponseJson<CntrPlanCntr[], null>> {
        const r = await this.datasource.manager.createQueryBuilder<CntrPlanCntr>(CntrPlanCntr, 'truck_cntr_plan_cntr')
            .where('truck_sn = :truck_sn', { truck_sn: query.plan_id })
            .getMany()
        return {
            code: ResponseCode.SuccessNoMsg,
            obj: r
        }
    }

    async exportQueryCntrInPlan(
        query: SqlQueryDto,
    ): Promise<ResponseJson<string, null>> {
        return this.sharedService.exportQueryToExcel<CntrInPlan>(
            CntrInPlan,
            query,
            {
                cntr_oper_cod: 'ClientKeyValue',
                cfs_cod: 'Cfs',
                cntr_owner: 'ClientKeyValue',
            },
        );
    }

    async createOutPlan<T extends OutPlanDto | PlanDto>(
        dto: T,
        cls: new () => T
    ): Promise<ResponseJson<CntrOutPlan, EmailResultType>> {

        const errors = await validate(plainToInstance(cls, dto));
        if (errors.length > 0) {
            throw new Error(Object.values(errors[0].constraints).join('   '));
        }
        const fees = dto.fees;
        const plan = omit(dto, ['fees']);
        plan.carry_id = 'T';

        plan.finish_id = 'N';

        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            const emailResult: EmailResultType = {
                status: '成功'
            }
            const insertPlanResult = await queryRunner.manager
                .createQueryBuilder()
                .insert()
                .into(CntrOutPlan)
                .values(plan)
                .execute();
            const plan_id = insertPlanResult.identifiers[0]['id'];

            if (!isNil(fees)) {
                fees.forEach((fee) => {
                    fee.truck_sn = plan_id;
                });

                await queryRunner.manager
                    .createQueryBuilder()
                    .insert()
                    .into(CntrPlanFee)
                    .values(fees)
                    .execute();
            }

            await queryRunner.commitTransaction();
            const newPlan = await this.datasource
                .createQueryBuilder<CntrOutPlan>(
                    CntrOutPlan,
                    'v_truck_cntr_outplan',
                )
                .leftJoinAndSelect(
                    'v_truck_cntr_outplan.fees',
                    'truck_cntr_plan_fee',
                )
                .where('v_truck_cntr_outplan.id = :id', { id: plan_id })
                .getOne();
            if (newPlan.need_email === 'Y') {
                const mailOption: EmailType = {};
                const clientmail = await this.datasource.createQueryBuilder()
                    .select(['email', 'client_nam_c'])
                    .from(Client, 'c_client_cod')
                    .where('id = :id', { id: newPlan.cntr_corp_cod })
                    .getRawOne();
                const cfsmail = await this.datasource.createQueryBuilder()
                    .select(['email', 'cfs_nam_c'])
                    .from(CyCfs, 'c_cy_cfs')
                    .where('id = :id', { id: newPlan.cfs_cod })
                    .getRawOne();

                if (clientmail?.email && cfsmail?.email) {
                    mailOption.to = `${clientmail['email']};${cfsmail['email']}`;
                    mailOption.subject = '新增出场计划';
                    mailOption.text = `
                            出场计划号：${newPlan.id}
                            出场计划名称: ${newPlan.plan_name}
                            尺寸： ${newPlan.cntr_siz_cod}
                            箱型： ${newPlan.cntr_typ_cod}
                            箱量： ${newPlan.cntr_num}
                            堆场： ${cfsmail.cfs_nam_c}
                        `;
                    const sendR = await this.sharedService.sendMail(mailOption);
                    if (sendR !== 'SUCCESS') {
                        emailResult.status = '失败';
                        emailResult.msg = sendR;
                    }
                } else {
                    emailResult.status = '失败';
                    emailResult.msg = '邮件发送失败，供应商或场站邮件地址错误';
                }
            }
            return {
                code: ResponseCode.SuccessWithInfo,
                obj: newPlan,
                msg: ['创建成功'],
                ext: emailResult
            };
        } catch (err) {
            await queryRunner.rollbackTransaction();
            throw err;
        } finally {
            await queryRunner.release();
        }
    }
    async updateOutPlan<T extends UpdateOutPlanDto | UpdateTransPlanDto>(
        dto: T,
        cls: new () => T,
        oper_nam: string
    ): Promise<ResponseJson<CntrOutPlan, EmailResultType>> {
        const fees = dto.fees as unknown[] as UpdatePlanFeeDto[];
        const plan = omit(dto, ['fees', 'updateFeeFlag', 'cntr_work_num']) as T;
        const errors = await validate(plainToInstance(cls, dto));
        if (errors.length > 0) {
            throw new Error(Object.values(errors[0].constraints).join('   '));
        }
        // const check = await checkDto(UpdateOutPlanDto, dto);
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            const emailResult: EmailResultType = {
                status: '成功'
            }
            // 检查是否已存在计划箱
            const existCntrs = await queryRunner.manager.createQueryBuilder<Cntr>(Cntr, 'cntr_file')
                .select('count(*) as count')
                .where('out_truck_sn = :plan_id', { plan_id: dto.id })
                .getRawOne();
            if (existCntrs.count > 0) {
                if (plan instanceof UpdateOutPlanDto) {
                    removeProperty(plan, ['stat_cod', 'cntr_corp_cod', 'cntr_siz_cod', 'cntr_typ_cod', 'dam_id',
                        'cntr_owner', 'cfs_cod', 'bill_no']);
                } else {
                    removeProperty(plan, ['stat_cod', 'cntr_siz_cod', 'cntr_typ_cod', 'dam_id',
                        'cntr_owner', 'cfs_cod', 'bill_no']);
                }
            }

            // 修改计划信息
            const dbPlan = await queryRunner.manager.createQueryBuilder<CntrPlan>(CntrPlan, 'truck_cntr_plan')
                .select('cntr_work_num', 'cntr_work_num')
                .where('id = :id', { id: dto['id'] })
                .getRawOne();
            plan.finish_id = (plan.cntr_num - (dbPlan?.cntr_work_num ?? 0) > 0) ? 'N' : 'Y';
            const updatePlanResult = await queryRunner.manager
                .createQueryBuilder()
                .update(CntrOutPlan)
                .set(plan)
                .where('id = :id', { id: dto['id'] })
                .execute();
            if (updatePlanResult.affected != 1) {
                throw new Error('无此计划，修改失败');
            }
            /* await queryRunner.manager.createQueryBuilder().delete().from(CntrPlanFee)
                .where('truck_sn = :id', { id: dto['id'] }).execute(); */
            const oldplanfees = await queryRunner.manager.createQueryBuilder<CntrPlanFee>(CntrPlanFee, 'truck_cntr_plan_fee')
                .where('truck_sn = :truck_sn', { truck_sn: dto['id'] })
                .getMany();
            if (!isNil(fees)) {
                const feeIds = []; // 已存在需保留的费用
                fees.forEach((fee) => {
                    fee.truck_sn = dto['id'];
                    if (!isNil(fee['id'])) {
                        feeIds.push(fee.id);
                    }
                });
                // 删除不需要的费用
                if (feeIds.length > 0) {
                    await queryRunner.manager
                        .createQueryBuilder()
                        .delete()
                        .from(CntrPlanFee)
                        .where('truck_sn = :truck_sn', { truck_sn: dto['id'] })
                        .andWhere('id not in (:...id)', { id: feeIds })
                        .execute();
                } else {
                    await queryRunner.manager
                        .createQueryBuilder()
                        .delete()
                        .from(CntrPlanFee)
                        .where('truck_sn = :truck_sn', { truck_sn: dto['id'] })
                        .execute();
                }

                fees.forEach(async (element) => {
                    if (isNil(element['id'])) {
                        await queryRunner.manager
                            .createQueryBuilder()
                            .insert()
                            .into(CntrPlanFee)
                            .values(element)
                            .execute();
                    } else {
                        const updateObj = omit(element, ['id', 'isEdit']);

                        await queryRunner.manager
                            .createQueryBuilder()
                            .update(CntrPlanFee)
                            .set(updateObj)
                            .where('id = :id', { id: element.id })
                            .execute();
                    }
                });
            }
            // 处理fee_det 
            if (dto.updateFeeFlag) {
                await this.changeFeedetCausedInPlanUpdate(oldplanfees, fees as CntrPlanFee[], dto['id'], plan.bill_no, oper_nam, queryRunner);
            }
            await queryRunner.commitTransaction();
            const newPlan = await this.datasource
                .createQueryBuilder<CntrOutPlan>(
                    CntrOutPlan,
                    'v_truck_cntr_outplan',
                )
                .leftJoinAndSelect(
                    'v_truck_cntr_outplan.fees',
                    'truck_cntr_plan_fee',
                )
                .where('v_truck_cntr_outplan.id = :id', { id: dto['id'] })
                .getOne();
            if (newPlan.need_email === 'Y') {
                const mailOption: EmailType = {};
                const clientmail = await this.datasource.createQueryBuilder()
                    .select(['email', 'client_nam_c'])
                    .from(Client, 'c_client_cod')
                    .where('id = :id', { id: newPlan.cntr_corp_cod })
                    .getRawOne();
                const cfsmail = await this.datasource.createQueryBuilder()
                    .select(['email', 'cfs_nam_c'])
                    .from(CyCfs, 'c_cy_cfs')
                    .where('id = :id', { id: newPlan.cfs_cod })
                    .getRawOne();

                if (clientmail?.email && cfsmail?.email) {
                    mailOption.to = `${clientmail['email']};${cfsmail['email']}`;
                    mailOption.subject = '修改出场计划';
                    mailOption.text = `
                        出场计划号：${newPlan.id}
                        出场计划名称: ${newPlan.plan_name}
                        尺寸： ${newPlan.cntr_siz_cod}
                        箱型： ${newPlan.cntr_typ_cod}
                        箱量： ${newPlan.cntr_num}
                        堆场： ${cfsmail.cfs_nam_c}
                    `;
                    const sendR = await this.sharedService.sendMail(mailOption);
                    if (sendR !== 'SUCCESS') {
                        emailResult.status = '失败';
                        emailResult.msg = sendR;
                    }
                } else {
                    emailResult.status = '失败';
                    emailResult.msg = '邮件发送失败，供应商或场站邮件地址错误';
                }
            }
            return {
                code: ResponseCode.SuccessWithInfo,
                obj: newPlan,
                msg: ['修改成功'],
                ext: emailResult
            };
        } catch (err) {
            await queryRunner.rollbackTransaction();

            throw err;
        } finally {
            await queryRunner.release();
        }
        // return await this.crud.update<Client>(Client, dto);
    }
    async deleteOutCntrPlan(dto: DeleteDto): Promise<ResponseJson> {
        return await this.crud.delete<CntrOutPlan>(CntrOutPlan, dto);
    }

    async queryCntrOutPlan(
        query: SqlQueryDto,
    ): Promise<ResponseJson<CntrOutPlan[], number>> {
        query.left = [
            /*  {
                property: 'truck_cntr_plan.cntrs',
                alias: 'truck_cntr_plan_cntr',
            }, */
            {
                property: 'v_truck_cntr_outplan.fees',
                alias: 'truck_cntr_plan_fee',
            },
        ];
        return await this.crud.query<CntrOutPlan>(CntrOutPlan, query);
    }
    async exportQueryCntrOutPlan(
        query: SqlQueryDto,
    ): Promise<ResponseJson<string, null>> {
        return this.sharedService.exportQueryToExcel<CntrOutPlan>(
            CntrOutPlan,
            query,
            {
                cntr_corp_cod: 'ClientKeyValue',
                cfs_cod: 'Cfs',
                cntr_owner: 'ClientKeyValue',
            },
        );
    }
    async queryCntr(
        query: SqlQueryDto,
    ): Promise<ResponseJson<VCntr[], number>> {
        return await this.crud.query<VCntr>(VCntr, query);
    }
    /**
     * 箱查询导出
     * @param query
     * @returns
     */
    async exportQueryCntr(
        query: SqlQueryDto,
    ): Promise<ResponseJson<string, null>> {
        return this.sharedService.exportQueryToExcel<VCntr>(VCntr, query, {
            current_stat: 'CntrStatus',
            cfs_cod: 'Cfs',
            cntr_oper_cod: 'ClientKeyValue',
            cntr_corp_cod: 'ClientKeyValue',
            cntr_owner: 'ClientKeyValue',
        });
    }

    /**
     * 进场计划导入箱
     * @param dto
     * @returns
     */
    async inplanImportCntr(
        dto: InplanImportCntrDto,
        operNam: string,
    ): Promise<ResponseJson<null, null>> {
        throw new Error('停用功能');
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            // 取计划、计划费用
            const plan = await queryRunner.manager
                .createQueryBuilder<CntrInPlan>(
                    CntrInPlan,
                    'v_truck_cntr_inplan',
                )
                .leftJoinAndSelect(
                    'v_truck_cntr_inplan.fees',
                    'truck_cntr_plan_fee',
                )
                .where('v_truck_cntr_inplan.id = :id', { id: dto.plan_id })
                .getOne();
            if (isNil(plan)) {
                throw new Error('取计划错误');
            }
            // 箱量检查
            if (plan.cntr_num - (plan.cntr_work_num ?? 0) < dto.cntrs.length) {
                throw new Error('超计划箱量，不能导入');
            }
            const tempCntr = omit(plan, [
                'id',
                //'stat_cod',
                'cntr_num',
                'plan_name',
                'cntr_work_num',
                'finish_id',
                'fees',
            ]);
            tempCntr['current_stat'] = '2';
            tempCntr['in_truck_sn'] = dto.plan_id;
            tempCntr['to_port_tim'] = plan.plan_date;
            tempCntr['in_dam_id'] = plan.dam_id;
            // tempCntr['last_cntr_stat'] = 'EIDXJC';
            // tempCntr['in_cy_stat'] = 'EIDXJC';
            // tempCntr['last_stat_tim'] = plan.plan_date;
            /* const tempFee = {
                bill_no: '',
                fee_unit_cod: 0,
                cntr_no: 0,
                fee_cod: '',
                amount: 0,
                currency_cod: '',
                in_truck_no: 0,
                fee_nam: '',
                fee_tim: null,
                fee_in_out: ''
            } */
            for (let index = 0; index < dto.cntrs.length; index++) {
                const cntr = dto.cntrs[index].trim().toUpperCase();

                tempCntr['cntr'] = cntr;

                Reflect.deleteProperty(tempCntr, 'id');
                // 生成箱子
                const cntr_no = await this.createCntr(
                    queryRunner,
                    tempCntr as unknown as CreateCntrDto,
                );
                if (cntr_no === 0) {
                    throw new Error(`箱号${cntr}进场错误`);
                }
                // 生成费用
                if (plan.fees.length > 0) {
                    for (let index = 0; index < plan.fees.length; index++) {
                        plan.fees[index]['bill_no'] = plan.bill_no;
                        plan.fees[index]['cntr_no'] = cntr_no;
                        plan.fees[index]['in_truck_sn'] = plan.id;
                        plan.fees[index]['fee_nam'] = operNam;
                        plan.fees[index]['fee_tim'] = plan.plan_date;
                        Reflect.deleteProperty(plan.fees[index], 'id');
                    }
                    await queryRunner.manager
                        .createQueryBuilder()
                        .insert()
                        .into(FeeDet)
                        .values(plan.fees)
                        .execute();
                }
            }
            // 修改计划箱量
            await queryRunner.manager
                .createQueryBuilder()
                .update(CntrInPlan)
                .set({
                    cntr_work_num: plan.cntr_work_num ?? 0 + dto.cntrs.length,
                })
                .where('id = :id', { id: dto.plan_id })
                .execute();
            await queryRunner.commitTransaction();
            return {
                code: ResponseCode.SuccessWithInfo,
                msg: ['进场成功'],
            };
        } catch (err) {
            await queryRunner.rollbackTransaction();
            throw err;
        } finally {
            await queryRunner.release();
        }
    }
    async cntrIngate(
        prama: CreateCntrDto,
        operNam: string,
    ): Promise<ResponseJson<Cntr, null>> {
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            const cntr = omit(prama, ['feedet']);
            cntr['last_cntr_stat'] = 'EIDXJC'; // 箱进场
            cntr['in_cy_stat'] = 'EIDXJC';
            cntr['last_stat_tim'] = cntr['to_port_tim'];
            cntr['current_stat'] = '2'; //在场
            cntr['in_cy_tim'] = cntr['to_port_tim'];
            cntr['cntr_create_kind'] = '0'; // 箱产生方式 0-进场  1-拼箱

            const cntr_no = await this.createCntr(
                queryRunner,
                cntr as CreateCntrDto,
            );
            const feedets = [];

            prama.feedet.forEach((f) => {
                feedets.push({
                    bill_no: cntr.bill_no,
                    cntr_no: cntr_no,
                    // cntr: cntr.cntr,
                    fee_nam: operNam,
                    fee_tim: cntr.to_port_tim,
                    ...f,
                });
            });
            for (let index = 0; index < feedets.length; index++) {
                const element = feedets[index];
                await this.businessService.createFeedet(element, queryRunner);
            }
            await queryRunner.commitTransaction();
            return {
                code: ResponseCode.SuccessWithInfo,
                msg: ['进场成功'],
            };
        } catch (err) {
            await queryRunner.rollbackTransaction();
            throw err;
        } finally {
            await queryRunner.release();
        }

        //return await this.crud.create<Cntr, CreateCntrDto>(Cntr, prama);
    }
    async gateout(prama: GateOutDto): Promise<ResponseJson<Cntr, null>> {
        prama.current_stat = '0';
        return this.updateCntr(prama);
    }
    /**
     * 在场箱修改
     * @param prama
     * @param operNam
     * @returns
     */
    async inCyCntrUpdate(
        prama: UpdateCntrDto,
        operNam: string,
    ): Promise<ResponseJson<Cntr, null>> {
        throw new Error('此功能停用');
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            const cntr = omit(prama, ['feedet', 'current_stat']);

            /* cntr['last_cntr_stat'] = 'EIDXJC'; // 箱进场
            cntr['in_cy_stat'] = 'EIDXJC';
            cntr['last_stat_tim'] = cntr['to_port_tim'];
            cntr['current_stat'] = '2'; //在场
            cntr['in_cy_tim'] = cntr['to_port_tim'];
            cntr['cntr_create_kind'] = '0'; // 箱产生方式 0-进场  1-拼箱 */

            const updateCntrResult = await this.updateCntr(cntr, queryRunner);
            /* const feedets = [];

            prama.feedet.forEach((f) => {
                feedets.push({ 
                    bill_no: cntr.bill_no,
                    cntr_no: prama.id,
                    cntr: cntr.cntr,
                    fee_nam: operNam,
                    fee_tim: cntr.to_port_tim,
                    ...f,
                });
            });
            const oldFeedets = await this.businessService.queryFeedet({
                where: {
                    cntr_no: {
                        equals: prama.id
                    }
                }
            });
            
            for (let index = 0; index < feedets.length; index++) {
                const element = feedets[index];
                await this.businessService.createFeedet(
                    element,
                    queryRunner.manager,
                );
            } */
            await queryRunner.commitTransaction();
            return {
                code: ResponseCode.SuccessWithInfo,
                msg: ['保存成功'],
            };
        } catch (err) {
            await queryRunner.rollbackTransaction();
            throw err;
        } finally {
            await queryRunner.release();
        }

        //return await this.crud.create<Cntr, CreateCntrDto>(Cntr, prama);
    }

    /**
     * 导入进场计划
     * @param file
     * @param palnID
     * @param operNam
     * @returns
     */
    async importInplans(
        file: any,
        operNam: string,
    ): Promise<ResponseJson<null, number>> {
        const workbook = XLSX.readFile(file.path, {
            type: 'binary',
            cellDates: true,
        });

        if (workbook.SheetNames.length === 0) {
            throw new Error('空文件');
        }
        const firstSheetName = workbook.SheetNames[0]; // 读取第一个工作表
        const sheet = workbook.Sheets[firstSheetName];
        const data = XLSX.utils.sheet_to_json(sheet, {
            header: 1,
            raw: false,
            dateNF: 'YYYY-MM-DD HH:mm:ss',
        }); // 将工作表转换为JSON

        if (data.length === 0) {
            throw new Error('空文件');
        }
        let insertNum = 0;
        // let updateNum = 0;
        const count = data.length;
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            const cntrOpers: [] = await queryRunner.manager
                .createQueryBuilder<Client>(Client, 'c_client_cod')
                .select(['id', 'client_nam_c', 'email'])
                .where("provid_id = 'Y' ")
                .execute();
            const cntrOwners: [] = await queryRunner.manager
                .createQueryBuilder<Client>(Client, 'c_client_cod')
                .select(['id', 'client_nam_c'])
                .where("cntr_owner_id = 'Y' ")
                .execute();
            const cfss: [] = await queryRunner.manager
                .createQueryBuilder<CyCfs>(CyCfs, 'c_cy_cfs')
                .select(['id', 'cfs_nam_c', 'email'])
                .execute();
            const mails: {
                client: number,
                clientEmail: string,
                cfs: number,
                cfsEmail: string,
                msg: string
            }[] = [];

            for (let index = 1; index < count; index++) {
                const row = data[index];
                const planObj = {} as unknown as InPlanDto;

                planObj.plan_name = row[0]?.toString().trim().toUpperCase();
                if (isNil(planObj.plan_name)) {
                    // throw new Error(`存在空箱号,导入失败`);
                    continue;
                }
                planObj.plan_date = new Date(row[1].toString().trim());
                planObj.cntr_siz_cod = row[2].toString().trim();
                planObj.cntr_typ_cod = row[3].toString().trim();
                planObj.cntr_num = parseInt(row[4]);
                const findCntrOper = cntrOpers.find((e) => {
                    return e['client_nam_c'] === row[5]?.toString().trim();
                });
                if (findCntrOper) {
                    planObj.cntr_oper_cod = findCntrOper['id'];
                } else {
                    throw new Error(
                        `计划${planObj.plan_name}供应商:${row[5]} 错误`,
                    );
                }
                const findcfs = cfss.find((e) => {
                    return e['cfs_nam_c'] === row[6]?.toString().trim();
                });
                if (findcfs) {
                    planObj.cfs_cod = findcfs['id'];
                } else {
                    throw new Error(
                        `计划${planObj.plan_name}站点:${row[6]} 错误`,
                    );
                }
                planObj.bill_no = row[7]?.toString().trim().toUpperCase();
                planObj.dam_id = row[8]?.toString().trim().toUpperCase();
                planObj.in_reason = row[9]?.toString().trim().toUpperCase();
                // 偷懒了，但愿别导其他计划
                planObj.stat_cod = EIDXJC;
                const findCntrOwner = cntrOwners.find((e) => {
                    return e['client_nam_c'] === row[10]?.toString().trim();
                });
                if (findCntrOwner) {
                    planObj.cntr_owner = findCntrOwner['id'];
                } else {
                    throw new Error(
                        `计划${planObj.plan_name}箱主:${row[10]} 错误`,
                    );
                }
                // planObj.free_days = parseInt(row[11]?.toString().trim());
                planObj.need_email = ['是', 'Y'].includes(row[12]?.toString().trim()) ? 'Y' : 'N';
                planObj.remark = row[13]?.toString().trim();
                const newPlanId = await this.insertInplan(planObj, InPlanDto, queryRunner);
                if (planObj.need_email === 'Y') {
                    const findMailIndex = mails.findIndex((m) => m.client === planObj.cntr_oper_cod && m.cfs === planObj.cfs_cod);
                    if (findMailIndex > -1) {
                        mails[findMailIndex].msg += `
    
                            进场计划号：${newPlanId}
                            进场计划名称: ${planObj.plan_name}
                            尺寸： ${planObj.cntr_siz_cod}
                            箱型： ${planObj.cntr_typ_cod}
                            箱量： ${planObj.cntr_num}
                            堆场： ${row[6]?.toString().trim()}
                        `
                    } else {
                        mails.push({
                            client: planObj.cntr_oper_cod,
                            clientEmail: findCntrOper['email'],
                            cfs: planObj.cfs_cod,
                            cfsEmail: findcfs['email'],
                            msg: `
                            进场计划号：${newPlanId}
                            进场计划名称: ${planObj.plan_name}
                            尺寸： ${planObj.cntr_siz_cod}
                            箱型： ${planObj.cntr_typ_cod}
                            箱量： ${planObj.cntr_num}
                            堆场： ${row[6]?.toString().trim()}
                            `
                        })
                    }
                }

                insertNum++;
            }
            await queryRunner.commitTransaction();
            for (let index = 0; index < mails.length; index++) {
                const element = mails[index];
                const mailOption: EmailType = {};
                if (element.clientEmail && element.cfsEmail) {
                    mailOption.to = `${element.clientEmail};${element.cfsEmail}`;
                    mailOption.subject = '新增进场计划';
                    mailOption.text = element.msg;
                    const sendR = await this.sharedService.sendMail(mailOption);
                }
            }
            return {
                code: ResponseCode.SuccessWithInfo,
                msg: [`新增${insertNum}条计划`,],
                ext: insertNum,
            };
        } catch (err) {
            await queryRunner.rollbackTransaction();
            throw err;
        } finally {
            await queryRunner.release();
        }
    }

    /**
     * 导入计划箱进场
     * @param file
     * @param palnID
     * @param operNam
     * @returns
     */
    async importCntrsFromExcel(
        file: any,
        palnID: number,
        operNam: string,
    ): Promise<ResponseJson<null, number>> {
        // throw new Error('停用此功能');
        const workbook = XLSX.readFile(file.path, {
            type: 'binary',
            cellDates: true,
        });

        if (workbook.SheetNames.length === 0) {
            throw new Error('空文件');
        }
        const firstSheetName = workbook.SheetNames[0]; // 读取第一个工作表
        const sheet = workbook.Sheets[firstSheetName];
        const data = XLSX.utils.sheet_to_json(sheet, {
            header: 1,
            raw: false,
            dateNF: 'YYYY-MM-DD HH:mm:ss',
        }); // 将工作表转换为JSON
        // console.log('data', data);

        if (data.length === 0) {
            throw new Error('空文件');
        }
        let insertNum = 0;
        // let updateNum = 0;
        const count = data.length;
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            const repairCorps: [] = await queryRunner.manager
                .createQueryBuilder<Client>(Client, 'c_client_cod')
                .select(['id', 'client_nam_c'])
                .where("repair_id = 'Y' ")
                .execute();
            const cfss: [] = await queryRunner.manager
                .createQueryBuilder<CyCfs>(CyCfs, 'c_cy_cfs')
                .select(['id', 'cfs_nam_c'])
                .execute();
            const findPlans = await queryRunner.manager
                .createQueryBuilder<CntrInPlan>(
                    CntrInPlan,
                    'v_truck_cntr_inplan',
                )
                .select([
                    'v_truck_cntr_inplan.cntr_siz_cod',
                    'v_truck_cntr_inplan.cntr_typ_cod',
                    'v_truck_cntr_inplan.cntr_oper_cod',
                    'v_truck_cntr_inplan.in_reason',
                    'v_truck_cntr_inplan.cfs_cod',
                    'v_truck_cntr_inplan.bill_no',
                    'v_truck_cntr_inplan.cntr_owner',
                    'v_truck_cntr_inplan.cntr_num',
                    'v_truck_cntr_inplan.cntr_work_num',
                ])
                .leftJoinAndSelect(
                    'v_truck_cntr_inplan.fees',
                    'truck_cntr_plan_fee',
                )
                .where('v_truck_cntr_inplan.id = :id', { id: palnID })
                .getOne();

            const maxCntrNum =
                findPlans.cntr_num - (findPlans.cntr_work_num ?? 0);


            for (let index = 1; index < count; index++) {
                const row = data[index];
                const cntrObj = {} as unknown as CreateCntrDto;
                const feedetObj = {} as unknown as FeedetDto;
                cntrObj.cntr = row[0]?.toString().trim().toUpperCase();
                if (isNil(cntrObj.cntr)) {
                    // throw new Error(`存在空箱号,导入失败`);
                    continue;
                }
                if (!checkCntrNo(cntrObj.cntr)) {
                    throw new Error(`箱号${cntrObj.cntr}箱号校验失败`);
                }
                if (insertNum >= maxCntrNum) {
                    throw new Error('超过计划剩余箱量');
                }
                // cntrObj.cntr_siz_cod = row[1]?.toString().trim().toUpperCase();
                // cntrObj.cntr_typ_cod = row[2]?.toString().trim().toUpperCase();
                cntrObj.in_truck_sn = palnID;
                cntrObj.cntr_siz_cod = findPlans.cntr_siz_cod;
                cntrObj.cntr_typ_cod = findPlans.cntr_typ_cod;
                cntrObj.cntr_oper_cod = findPlans.cntr_oper_cod;
                cntrObj.in_reason = findPlans.in_reason;
                cntrObj.cfs_cod = findPlans.cfs_cod;
                cntrObj.bill_no = findPlans.bill_no;
                cntrObj.cntr_owner = findPlans.cntr_owner;

                cntrObj.to_port_tim = new Date(row[3].toString().trim());

                cntrObj.in_ship_nam = row[4]?.toString().trim().toUpperCase();
                cntrObj.in_voyage = row[5]?.toString().trim().toUpperCase();
                // cntrObj.bill_no = row[6]?.toString().trim().toUpperCase();
                // cntrObj.in_dam_id = row[7]?.toString().trim().toUpperCase();
                // cntrObj.in_reason = row[8]?.toString().trim();
                // cntrObj['u_dam_id'] = row[9].trim();
                cntrObj.dam_id = row[9]?.toString().trim().toUpperCase();
                cntrObj.cntr_net_wgt = isNaN(
                    parseFloat(row[10]?.toString().trim()),
                )
                    ? null
                    : parseFloat(row[10]?.toString().trim());
                cntrObj.scale_wgt = isNaN(
                    parseFloat(row[11]?.toString().trim()),
                )
                    ? null
                    : parseFloat(row[11]?.toString().trim());
                cntrObj.cntr_date = new Date(row[12]?.toString().trim());
                /* const findcfs = cfss.find((e) => {
                    return e['cfs_nam_c'] === row[13]?.toString().trim();
                });
                if (findcfs) {
                    cntrObj.cfs_cod = findcfs['id'];
                } else {
                    throw new Error(
                        `箱号${cntrObj.cntr}堆场号:${row[13]} 错误`,
                    );
                } */
                cntrObj.free_days = isNaN(parseInt(row[14]?.toString().trim()))
                    ? null
                    : parseInt(row[14]?.toString().trim());
                const repair_corp = row[15]?.toString().trim();
                const repair_fee = row[16]?.toString().trim()
                    ? parseFloat(row[16]?.toString().trim())
                    : undefined;
                const repair_tim = row[17]?.toString().trim()
                    ? new Date(row[17]?.toString().trim())
                    : undefined;
                cntrObj.refmachine_brand = row[18]
                    ?.toString()
                    .trim()
                    .toUpperCase();
                cntrObj.refmachine_model = row[19]
                    ?.toString()
                    .trim()
                    .toUpperCase();
                cntrObj.refmachine_date = new Date(row[20]?.toString().trim());
                if (repair_corp || repair_fee || repair_tim) {
                    // 如果有修箱费用
                    /* feedetObj.bill_no = cntrObj.bill_no;

                    if (isNil(repair_corp)) {
                        throw new Error(`箱号${cntrObj.cntr}修箱公司不能为空`);
                    }
                    const repaircorp = repairCorps.find((e) => {
                        return e['client_nam_c'] === repair_corp;
                    });
                    if (repaircorp) {
                        feedetObj.fee_unit_cod = repaircorp['id'];
                    } else {
                        throw new Error(`箱号${cntrObj.cntr}修箱公司错误`);
                    }
                    if (isNil(repair_fee)) {
                        throw new Error(`箱号${cntrObj.cntr}修箱费用不能为空`);
                    }
                    if (isNil(repair_tim)) {
                        throw new Error(`箱号${cntrObj.cntr}修箱日期不能为空`);
                    }

                    feedetObj.fee_cod = '0106'; // 修箱费
                    feedetObj.amount = repair_fee;
                    feedetObj.currency_cod = 'RMB';
                    feedetObj.fee_nam = operNam;
                    feedetObj.fee_tim = repair_tim;
                    feedetObj.fee_in_out = 'O'; */
                }
                const cntr = await queryRunner.manager
                    .createQueryBuilder<Cntr>(Cntr, 'cntr_file')
                    .where('current_stat = :stat and cntr = :cntr', {
                        stat: '2',
                        cntr: cntrObj['cntr'],
                    })
                    .getOne();
                if (isNil(cntr)) {
                    // insert
                    // cntrObj['last_cntr_stat'] = 'EIDXJC'; // 箱进场
                    // cntrObj['in_cy_stat'] = 'EIDXJC';
                    // cntrObj['last_stat_tim'] = cntrObj['to_port_tim'];
                    cntrObj['current_stat'] = '2'; //在场
                    // cntrObj['in_cy_tim'] = cntrObj['to_port_tim'];

                    const cntr_no = await this.createCntr(queryRunner, cntrObj);
                    if (cntr_no === 0) {
                        throw new Error(`箱号${cntrObj.cntr}进场错误`);
                    }

                    /* if (!isEmpty(feedetObj)) {
                        feedetObj.cntr_no = cntr_no;
                        await this.businessService.createFeedet(
                            feedetObj,
                            queryRunner.manager,
                        );
                    } */

                    for (
                        let index = 0;
                        index < findPlans.fees.length;
                        index++
                    ) {
                        const element = findPlans.fees[index];
                        // feedetObj.in_truck_sn = palnID;
                        if (['0105', '0108'].includes(element.fee_cod)) {
                            continue;
                        }
                        feedetObj.cntr_no = cntr_no;
                        feedetObj.fee_owner = element.fee_owner;
                        feedetObj.fee_typ_cod = element.fee_typ_cod;
                        feedetObj.fee_cod = element.fee_cod;
                        feedetObj.fee_unit_cod = element.fee_unit_cod;
                        feedetObj.amount = element.amount;
                        feedetObj.currency_cod = element.currency_cod;
                        feedetObj.fee_nam = operNam;
                        feedetObj.fee_tim = cntrObj.to_port_tim;
                        feedetObj.fee_in_out = element.fee_in_out;
                        feedetObj.bill_no = findPlans.bill_no;
                        await this.businessService.createFeedet(
                            feedetObj,
                            queryRunner,
                        );
                    }
                    insertNum++;
                } else {
                    // update
                    continue;
                    // throw new Error(`箱号${cntrObj.cntr}已在场不能导入`);
                    /* cntrObj['id'] = cntr.id;
                    if (
                        await this.updateCntr(
                            cntrObj as UpdateCntrDto,
                            queryRunner,
                        )
                    ) {
                        if (!isEmpty(feedetObj)) {
                            feedetObj.cntr_no = cntr.id;
                            await this.businessService.updateFeedetGroupFeeByCntrNo(
                                cntr.id,
                                feedetObj as UpdateFeedetDto,
                                queryRunner,
                            );
                        }
                        updateNum++;
                    } */
                }
            }
            await queryRunner.manager
                .createQueryBuilder()
                .update(CntrInPlan)
                .set({
                    cntr_work_num: (findPlans.cntr_work_num ?? 0) + insertNum,
                    finish_id:
                        (findPlans.cntr_work_num ?? 0) + insertNum >=
                            findPlans.cntr_num
                            ? 'Y'
                            : 'N',
                })
                .where('id = :id', { id: palnID })
                .execute();
            await queryRunner.commitTransaction();
            return {
                code: ResponseCode.SuccessWithInfo,
                msg: [`新增${insertNum}条箱子`],
                ext: insertNum,
            };
        } catch (err) {
            await queryRunner.rollbackTransaction();
            // console.log(err);
            throw err;
        } finally {
            await queryRunner.release();
        }
    }

    /**
     * 导入进场计划及箱号 
     *    增加 2024-12-13
     *    1、 尺寸、箱型、供应商、站点、箱主直接给箱子， 允许箱子和计划以上字段不一致
     *    1、导入费用
     *    2、自动生成站间调箱动态
     */
    async importPlanAndCntrPlus(
        file: any, oper_nam: string
    ): Promise<ResponseJson<null, null>> {
        const workbook_source = new Excel.Workbook();
        await workbook_source.xlsx.readFile(file.path);
        if (workbook_source.worksheets.length === 0) {
            throw new Error('空文件');
        }
        let insertPlanNum = 0;
        let insertCntrNum = 0;
        const sheet = workbook_source.getWorksheet(1);
        const rowCount = sheet.rowCount;
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            const cfss: [] = await queryRunner.manager
                .createQueryBuilder<CyCfs>(CyCfs, 'c_cy_cfs')
                .select(['id', 'cfs_nam_c'])
                .execute();
            const clients: [] = await queryRunner.manager
                .createQueryBuilder<Client>(Client, 'c_client_cod')
                .select(['id', 'client_nam_c'])
                .execute();
            const cntrStats = await queryRunner.manager
                .createQueryBuilder<CntrStat>(CntrStat, 'c_cntr_stat')
                .select(['stat_cod', 'stat_nam'])
                .where('carry_id is not null')
                .execute();

            const curPlan = {} as unknown as InPlanDto;
            let curPlanId = 0;
            const feeCodNum = [
                '0106', // 修理费
                '0112', // PTI费
                '35eb56', // 送箱费
                '58fd79', // 退租机械费
                '6793d8', // 起租机械费
            ];
            const feeColumnNum = 4;
            for (let index = 2; index <= rowCount; index++) {
                const row = sheet.getRow(index);
                // console.log(row);

                const planName = row.getCell('A').value?.toString().trim();
                if (!isNil(planName)) { // 新计划
                    deleteAllProperties(curPlan);
                    curPlan.plan_name = planName;
                    curPlan.plan_date = new Date(row.getCell('B').value?.toString().trim());
                    const findCntrStat = cntrStats.find((e) => {
                        return e['stat_nam'] === row.getCell('S').value?.toString().trim();
                    });
                    if (findCntrStat) {
                        curPlan.stat_cod = findCntrStat['stat_cod'];
                    } else {
                        throw new Error(`计划${planName}作业目的错误`);
                    }
                    // curPlan.stat_cod = EIDXJC;


                    curPlan.cntr_siz_cod = row.getCell('C').value?.toString().trim();
                    curPlan.cntr_typ_cod = row.getCell('D').value?.toString().trim();
                    curPlan.cntr_num = parseInt(row.getCell('E').value?.toString().trim());
                    // curPlan.finish_id = 'Y';
                    const findCntrOper = clients.find((e) => {
                        return e['client_nam_c'] === row.getCell('F').value?.toString().trim();
                    });
                    if (findCntrOper) {
                        curPlan.cntr_oper_cod = findCntrOper['id'];
                    } else {
                        throw new Error(
                            `计划${planName}供应商:${row.getCell('F').value?.toString().trim()} 错误`,
                        );
                    }
                    const findCfs = cfss.find((e) => {
                        return e['cfs_nam_c'] === row.getCell('G').value?.toString().trim();
                    });
                    if (findCfs) {
                        curPlan.cfs_cod = findCfs['id'];
                    } else {
                        throw new Error(
                            `计划${planName}堆场:${row.getCell('G').value?.toString().trim()} 错误`,
                        );
                    }
                    curPlan.bill_no = row.getCell('H').value?.toString().trim();
                    curPlan.dam_id = row.getCell('I').value?.toString().trim();
                    curPlan.in_reason = row.getCell('J').value?.toString().trim();
                    const findCntrOwner = clients.find((e) => {
                        return e['client_nam_c'] === row.getCell('K').value?.toString().trim();
                    });
                    if (findCntrOwner) {
                        curPlan.cntr_owner = findCntrOwner['id'];
                    } else {
                        throw new Error(
                            `计划${planName}箱主:${row.getCell('K').value?.toString().trim()} 错误`,
                        );
                    }
                    curPlan.remark = row.getCell('L').value?.toString().trim();
                    curPlanId = await this.insertInplan(curPlan, InPlanDto, queryRunner);
                    insertPlanNum++;
                }
                if (curPlanId > 0) {
                    const cntrObj = {} as unknown as CreateCntrDto;
                    cntrObj.cntr = row.getCell('M').value?.toString().trim();
                    cntrObj.to_port_tim = new Date(row.getCell('N').value?.toString().trim());
                    cntrObj.dam_id = row.getCell('O').value?.toString().trim();
                    if (isNil(cntrObj.cntr)) {
                        throw new Error(`第${index}行箱号空`);
                    }
                    if (isNil(cntrObj.to_port_tim)) {
                        throw new Error(`第${index}行进场时间空`);
                    }
                    if (isNil(cntrObj.dam_id)) {
                        throw new Error(`第${index}行进场箱况空`);
                    }
                    cntrObj.cntr_net_wgt = row.getCell('P').value ? (parseFloat(row.getCell('P').value?.toString().trim())) : undefined;
                    cntrObj.scale_wgt = row.getCell('Q').value ? parseFloat(row.getCell('Q').value?.toString().trim()) : undefined;
                    cntrObj.cntr_date = new Date(row.getCell('R').value?.toString().trim());
                    cntrObj.in_truck_sn = curPlanId;
                    cntrObj.current_stat = '2';
                    cntrObj.cntr_siz_cod = row.getCell('C').value?.toString().trim();
                    cntrObj.cntr_typ_cod = row.getCell('D').value?.toString().trim();
                    const findCntrOper = clients.find((e) => {
                        return e['client_nam_c'] === row.getCell('F').value?.toString().trim();
                    });
                    if (findCntrOper) {
                        cntrObj.cntr_oper_cod = findCntrOper['id'];
                    } else {
                        throw new Error(
                            `箱号${cntrObj.cntr}供应商:${row.getCell('F').value?.toString().trim()} 错误`,
                        );
                    }
                    const findCfs = cfss.find((e) => {
                        return e['cfs_nam_c'] === row.getCell('G').value?.toString().trim();
                    });
                    if (findCfs) {
                        cntrObj.cfs_cod = findCfs['id'];
                    } else {
                        throw new Error(
                            `箱号${cntrObj.cntr}堆场:${row.getCell('G').value?.toString().trim()} 错误`,
                        );
                    }

                    let cfs2: number; // 调入站点

                    if (row.getCell('T')?.value) {
                        const findCfs2 = cfss.find((e) => {
                            return e['cfs_nam_c'] === row.getCell('T').value?.toString().trim();
                        });
                        if (findCfs2) {
                            cfs2 = findCfs2['id'];
                        } else {
                            throw new Error(`箱号${cntrObj.cntr}站点2:${row.getCell('T').value?.toString().trim()} 错误`);
                        }
                    }
                    cntrObj.bill_no = row.getCell('H').value?.toString().trim();
                    cntrObj.dam_id = row.getCell('O').value?.toString().trim();
                    cntrObj.in_reason = row.getCell('J').value?.toString().trim();
                    const findCntrOwner = clients.find((e) => {
                        return e['client_nam_c'] === row.getCell('K').value?.toString().trim();
                    });
                    if (findCntrOwner) {
                        cntrObj.cntr_owner = findCntrOwner['id'];
                    } else {
                        throw new Error(
                            `箱号${cntrObj.cntr}箱主:${row.getCell('K').value?.toString().trim()} 错误`,
                        );
                    }
                    const cntr_no = await this.createCntr(queryRunner, cntrObj);
                    if (cntr_no === 0) {
                        throw new Error(`箱号${cntrObj.cntr}进场错误`);
                    }
                    insertCntrNum++;

                    // 创建箱进场动态
                    const cntrStatObj = {} as unknown as PortCntrStatDto;
                    cntrStatObj.cntr_no = cntr_no;
                    cntrStatObj.cntr = cntrObj.cntr;
                    // cntrStatObj.current_stat = '2';
                    const findCntrStat = cntrStats.find((e) => {
                        return e['stat_nam'] === row.getCell('S').value?.toString().trim();
                    });
                    if (findCntrStat) {
                        cntrStatObj.cntr_stat_cod = findCntrStat.stat_cod;
                    } else {
                        throw new Error(`箱号${cntrObj.cntr}作业目的错误`);
                    }
                    // cntrStatObj.cntr_stat_cod = EIDXJC;
                    cntrStatObj.stat_tim = cntrObj.to_port_tim;
                    cntrStatObj.cfs_cod = cntrObj.cfs_cod;
                    cntrStatObj.plan_id = curPlanId;
                    cntrStatObj.dam_id = cntrObj.dam_id;

                    await this.createPortCntrStat(cntrStatObj, queryRunner);
                    // 创建调箱出、调箱进动态  如果导入表中提供了站点2,则自动创建调箱出、调箱进两个动态
                    if (cfs2) {
                        const diaoOutObj = {} as unknown as PortCntrStatDto;
                        diaoOutObj.cntr_no = cntr_no;
                        diaoOutObj.cntr = cntrObj.cntr;
                        diaoOutObj.cntr_stat_cod = EOZYXC; // 站间调箱出场
                        diaoOutObj.stat_tim = dayjs(cntrObj.to_port_tim).add(1, 's').toDate();
                        diaoOutObj.cfs_cod = null;
                        diaoOutObj.plan_id = 107;
                        diaoOutObj.dam_id = cntrObj.dam_id;
                        await this.createPortCntrStat(diaoOutObj, queryRunner);
                        const diaoInObj = {} as unknown as PortCntrStatDto;
                        diaoInObj.cntr_no = cntr_no;
                        diaoInObj.cntr = cntrObj.cntr;
                        diaoInObj.cntr_stat_cod = EIZYXT; // 站间调箱进场
                        diaoInObj.stat_tim = dayjs(cntrObj.to_port_tim).add(2, 's').toDate();
                        diaoInObj.cfs_cod = cfs2
                        diaoInObj.plan_id = 108;
                        diaoInObj.dam_id = cntrObj.dam_id;
                        await this.createPortCntrStat(diaoInObj, queryRunner);
                    }
                    // 处理费用


                    const findFeeTyp = await queryRunner.manager
                        .createQueryBuilder<FeeTyp>(FeeTyp, 'c_fee_typ')
                        .select()
                        .where('fee_typ_nam = :fee_typ_nam', {
                            fee_typ_nam: row.getCell('U').value?.toString().trim()
                        })
                        .getOne();

                    for (let indexFeeNum = 0; indexFeeNum < feeCodNum.length; indexFeeNum++) {
                        const feeAmount = parseFloat(row.getCell(22 + indexFeeNum * feeColumnNum + 0).value?.toString().trim());
                        if (feeAmount) {
                            const feeObj = {} as unknown as FeedetDto;

                            feeObj.cntr_no = cntr_no;
                            feeObj.fee_owner = cntrObj.cntr_owner;

                            if (!findFeeTyp) {
                                throw new Error(`箱号${cntrObj.cntr}一级费目错误`);
                            }
                            feeObj.fee_typ_cod = findFeeTyp.fee_typ_cod;
                            feeObj.fee_cod = feeCodNum[indexFeeNum];
                            // fixObj.fee_unit_cod = element.fee_unit_cod;
                            const findFeeUnit = clients.find((e) => {
                                return e['client_nam_c'] === row.getCell(22 + indexFeeNum * feeColumnNum + 3)?.value.toString().trim();
                            });
                            if (findFeeUnit) {
                                feeObj.fee_unit_cod = findFeeUnit['id'];
                            } else {
                                throw new Error(
                                    `箱号${cntrObj.cntr}费用往来单位错误`,
                                );
                            }
                            feeObj.amount = feeAmount;
                            const feeCurrency = row.getCell(22 + indexFeeNum * feeColumnNum + 1)?.value.toString().trim().toUpperCase();
                            feeObj.currency_cod = feeCurrency;
                            feeObj.fee_nam = oper_nam;
                            feeObj.fee_tim = cntrObj.to_port_tim;
                            const feeInOut = row.getCell(22 + indexFeeNum * feeColumnNum + 2)?.value.toString().trim();
                            feeObj.fee_in_out = feeInOut.includes('收') ? 'I' : 'O';
                            feeObj.lease_sn = curPlanId;
                            await this.businessService.createFeedet(
                                feeObj,
                                queryRunner,
                            );
                        }
                    }


                } else {
                    throw new Error('计划号错误');
                }
            }
            await queryRunner.commitTransaction();
            return {
                code: ResponseCode.SuccessWithInfo,
                msg: [`成功导入${insertPlanNum}条计划，${insertCntrNum}个箱子`]
            }
        } catch (error) {
            await queryRunner.rollbackTransaction();
            throw error;
        } finally {
            await queryRunner.release();
        }

    }
    /**
     * 导入进场计划及箱号
     */
    async importPlanAndCntr(
        file: any,
    ): Promise<ResponseJson<null, null>> {

        const workbook = XLSX.readFile(file.path, {
            type: 'binary',
            cellDates: true,
        });

        if (workbook.SheetNames.length === 0) {
            throw new Error('空文件');
        }
        const firstSheetName = workbook.SheetNames[0]; // 读取第一个工作表
        const sheet = workbook.Sheets[firstSheetName];
        const data = XLSX.utils.sheet_to_json(sheet, {
            header: 1,
            raw: false,
            dateNF: 'YYYY-MM-DD HH:mm:ss',
        });
        if (data.length === 0) {
            throw new Error('空文件');
        }
        let insertPlanNum = 0;
        let insertCntrNum = 0;
        const count = data.length;
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            const cfss: [] = await queryRunner.manager
                .createQueryBuilder<CyCfs>(CyCfs, 'c_cy_cfs')
                .select(['id', 'cfs_nam_c'])
                .execute();
            const clients: [] = await queryRunner.manager
                .createQueryBuilder<Client>(Client, 'c_client_cod')
                .select(['id', 'client_nam_c'])
                .execute();
            const curPlan = {} as unknown as InPlanDto;
            let curPlanId = 0;

            for (let index = 1; index < count; index++) {
                const element = data[index];
                const planName = element[0]?.toString().trim();
                if (!isNil(planName)) { // 新计划
                    deleteAllProperties(curPlan);
                    curPlan.plan_name = planName;
                    curPlan.plan_date = new Date(element[1]?.toString().trim());
                    curPlan.stat_cod = EIDXJC;
                    curPlan.cntr_siz_cod = element[2].toString().trim();
                    curPlan.cntr_typ_cod = element[3].toString().trim();
                    curPlan.cntr_num = parseInt(element[4].toString().trim());

                    const findCntrOper = clients.find((e) => {
                        return e['client_nam_c'] === element[5]?.toString().trim();
                    });
                    if (findCntrOper) {
                        curPlan.cntr_oper_cod = findCntrOper['id'];
                    } else {
                        throw new Error(
                            `计划${planName}供应商:${element[5].toString().trim()} 错误`,
                        );
                    }
                    const findCfs = cfss.find((e) => {
                        return e['cfs_nam_c'] === element[6]?.toString().trim();
                    });
                    if (findCfs) {
                        curPlan.cfs_cod = findCfs['id'];
                    } else {
                        throw new Error(
                            `计划${planName}堆场:${element[6].toString().trim()} 错误`,
                        );
                    }
                    curPlan.bill_no = element[7]?.toString().trim();
                    curPlan.dam_id = element[8]?.toString().trim();
                    curPlan.in_reason = element[9]?.toString().trim();
                    const findCntrOwner = clients.find((e) => {
                        return e['client_nam_c'] === element[10]?.toString().trim();
                    });
                    if (findCntrOwner) {
                        curPlan.cntr_owner = findCntrOwner['id'];
                    } else {
                        throw new Error(
                            `计划${planName}箱主:${element[10].toString().trim()} 错误`,
                        );
                    }
                    curPlan.remark = element[11]?.toString().trim();

                    curPlanId = await this.insertInplan(curPlan, InPlanDto, queryRunner);
                    insertPlanNum++;
                }
                if (curPlanId > 0) {
                    const cntrObj = {} as unknown as CreateCntrDto;
                    cntrObj.cntr = element[12]?.toString().trim();
                    cntrObj.to_port_tim = new Date(element[13]?.toString().trim());
                    cntrObj.dam_id = element[14]?.toString().trim();
                    if (isNil(cntrObj.cntr)) {
                        throw new Error(`第${index}行箱号空`);
                    }
                    if (isNil(cntrObj.to_port_tim)) {
                        throw new Error(`第${index}行进场时间空`);
                    }
                    if (isNil(cntrObj.dam_id)) {
                        throw new Error(`第${index}行箱况空`);
                    }
                    cntrObj.cntr_net_wgt = parseFloat(element[15]?.toString().trim());
                    cntrObj.scale_wgt = parseFloat(element[16]?.toString().trim());
                    cntrObj.cntr_date = new Date(element[17]?.toString().trim());
                    cntrObj.in_truck_sn = curPlanId;
                    cntrObj.current_stat = '2';
                    cntrObj.cntr_siz_cod = curPlan.cntr_siz_cod;
                    cntrObj.cntr_typ_cod = curPlan.cntr_typ_cod;
                    cntrObj.cntr_oper_cod = curPlan.cntr_oper_cod;
                    cntrObj.in_reason = curPlan.in_reason;
                    cntrObj.cfs_cod = curPlan.cfs_cod;
                    cntrObj.bill_no = curPlan.bill_no;
                    cntrObj.cntr_owner = curPlan.cntr_owner;
                    const cntr_no = await this.createCntr(queryRunner, cntrObj);
                    if (cntr_no === 0) {
                        throw new Error(`箱号${cntrObj.cntr}进场错误`);
                    }
                    insertCntrNum++;
                    // 创建箱动态
                    const cntrStatObj = {} as unknown as PortCntrStatDto;
                    cntrStatObj.cntr_no = cntr_no;
                    cntrStatObj.cntr = cntrObj.cntr;
                    // cntrStatObj.current_stat = '2';
                    cntrStatObj.cntr_stat_cod = EIDXJC;
                    cntrStatObj.stat_tim = cntrObj.to_port_tim;
                    cntrStatObj.cfs_cod = curPlan.cfs_cod;
                    cntrStatObj.plan_id = curPlanId;
                    cntrStatObj.dam_id = cntrObj.dam_id;
                    await this.createPortCntrStat(cntrStatObj, queryRunner);
                } else {
                    throw new Error('计划号错误！');
                }
            }
            await queryRunner.commitTransaction();
            return {
                code: ResponseCode.SuccessWithInfo,
                msg: [`成功导入${insertPlanNum}条计划，${insertCntrNum}个箱子`]
            }
        }
        catch (err) {
            await queryRunner.rollbackTransaction();
            throw err;
        }
        finally {
            await queryRunner.release();
        }
    }

    async importGateOutFromExcel(
        file: any,
        planID: number,
        operNam: string,
    ): Promise<ResponseJson<null, null>> {
        const workbook = XLSX.readFile(file.path, {
            type: 'binary',
            cellDates: true,
        });

        if (workbook.SheetNames.length === 0) {
            throw new Error('空文件');
        }
        const firstSheetName = workbook.SheetNames[0]; // 读取第一个工作表
        const sheet = workbook.Sheets[firstSheetName];
        const data = XLSX.utils.sheet_to_json(sheet, {
            header: 1,
            raw: false,
            dateNF: 'YYYY-MM-DD HH:mm:ss',
        }); // 将工作表转换为JSON


        if (data.length === 0) {
            throw new Error('空文件');
        }

        const count = data.length;
        let insertNum = 0;
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            const cntr_corp_cods: [] = await queryRunner.manager
                .createQueryBuilder<Client>(Client, 'c_client_cod')
                .select(['id', 'client_nam_c'])
                .where("custom_id = 'Y' ")
                .execute();

            const cfss: [] = await queryRunner.manager
                .createQueryBuilder<CyCfs>(CyCfs, 'c_cy_cfs')
                .select(['id', 'cfs_nam_c'])
                .execute();
            const findPlan = await queryRunner.manager
                .createQueryBuilder<CntrOutPlan>(
                    CntrOutPlan,
                    'v_truck_cntr_outplan',
                )
                .select([
                    'v_truck_cntr_outplan.cntr_corp_cod',
                    'v_truck_cntr_outplan.cfs_cod',
                    'v_truck_cntr_outplan.bill_no',
                    'v_truck_cntr_outplan.cntr_num',
                    'v_truck_cntr_outplan.cntr_work_num',
                ])
                .leftJoinAndSelect(
                    'v_truck_cntr_outplan.fees',
                    'truck_cntr_plan_fee',
                )
                .where('v_truck_cntr_outplan.id = :id', { id: planID })
                .getOne();


            const maxCntrNum =
                findPlan.cntr_num - (findPlan.cntr_work_num ?? 0);
            /* if (findPlan.cntr_num - (findPlan.cntr_work_num ?? 0) < count - 1) {
                throw new Error('超过计划剩余箱量');
            } */
            for (let index = 1; index < count; index++) {
                const row = data[index];
                if (isNil(row[1]?.toString())) {
                    continue;
                }
                const cntrObj = {} as unknown as GateOutDto;
                const feedetObj = {} as unknown as FeedetDto;
                // cntrObj.release_no = row[0]?.toString().trim().toUpperCase();
                cntrObj.out_truck_sn = planID;

                cntrObj.release_no = findPlan.bill_no;
                if (isNil(cntrObj.release_no)) {
                    throw new Error(`计划放箱号为空,导入失败`);
                }
                if (insertNum >= maxCntrNum) {
                    throw new Error('超过计划剩余箱量');
                }

                const findR = await queryRunner.manager
                    .createQueryBuilder<Cntr>(Cntr, 'cntr_file')
                    .where("cntr = :cntr and current_stat = '2'", {
                        cntr: row[1]?.toString().trim().toUpperCase(),
                    })
                    .getOne();

                if (findR) {
                    cntrObj.id = findR.id;
                } else {
                    throw new Error(
                        `箱号${row[1]?.toString().trim().toUpperCase()}不在场`,
                    );
                }

                if (
                    findR.cntr_siz_cod !==
                    row[2]?.toString().trim().toUpperCase()
                ) {
                    throw new Error(`箱号${findR.cntr}尺寸不相符`);
                }
                if (
                    findR.cntr_typ_cod !==
                    row[3]?.toString().trim().toUpperCase()
                ) {
                    throw new Error(`箱号${findR.cntr}箱型不相符`);
                }
                const findcfs = cfss.find((e) => {
                    return e['id'] === findR.cfs_cod;
                });
                if (findcfs) {
                    if (findcfs['id'] !== findR.cfs_cod) {
                        throw new Error(`箱号${findR.cntr}堆场不一致错误1`);
                    }
                } else {
                    throw new Error(`箱号${findR.cntr}堆场不一致错误2`);
                }
                cntrObj.cntr_corp_cod = findPlan.cntr_corp_cod;
                /* const findCntr_corp_cod = cntr_corp_cods.find((e) => {
                    return e['client_nam_c'] === row[5]?.toString().trim();
                });
                if (findCntr_corp_cod) {
                    cntrObj.cntr_corp_cod = findCntr_corp_cod['id'];
                } else {
                    throw new Error(
                        `客户${row[5]?.toString().trim()}不在客户代码表`,
                    );
                } */
                cntrObj.current_stat = '0';
                if (!row[8]?.toString().trim()) {
                    throw new Error(`箱号${findR.cntr}提箱日期错误`);
                }
                cntrObj.allow_date = new Date(row[7]?.toString().trim());
                cntrObj.leav_port_tim = new Date(row[8]?.toString().trim());

                cntrObj.out_truck_no_c = row[9]
                    ?.toString()
                    .trim()
                    .toUpperCase();
                cntrObj.out_truck_tel = row[10]
                    ?.toString()
                    .trim()
                    .toUpperCase();


                await queryRunner.manager
                    .createQueryBuilder()
                    .update(Cntr)
                    .set(omit(cntrObj, ['id']))
                    .where('id = :id', { id: cntrObj.id })
                    .execute();
                insertNum++;
                for (let index = 0; index < findPlan.fees.length; index++) {
                    const element = findPlan.fees[index];
                    // feedetObj.out_truck_sn = planID;
                    feedetObj.cntr_no = findR.id;
                    feedetObj.fee_owner = element.fee_owner;
                    feedetObj.fee_typ_cod = element.fee_typ_cod;
                    feedetObj.fee_cod = element.fee_cod;
                    feedetObj.fee_unit_cod = element.fee_unit_cod;
                    feedetObj.amount = element.amount;
                    feedetObj.currency_cod = element.currency_cod;
                    feedetObj.fee_nam = operNam;
                    feedetObj.fee_tim = cntrObj.leav_port_tim;
                    feedetObj.fee_in_out = element.fee_in_out;
                    feedetObj.bill_no = findPlan.bill_no;
                    await this.businessService.createFeedet(
                        feedetObj,
                        queryRunner,
                    );
                }
                // await this.updateCntr(cntrObj, queryRunner);
            }
            await queryRunner.manager
                .createQueryBuilder()
                .update(CntrOutPlan)
                .set({
                    cntr_work_num: (findPlan.cntr_work_num ?? 0) + insertNum,
                    finish_id:
                        (findPlan.cntr_work_num ?? 0) + insertNum >=
                            findPlan.cntr_num
                            ? 'Y'
                            : 'N',
                })
                .where('id = :id', { id: planID })
                .execute();
            await queryRunner.commitTransaction();
            return {
                code: ResponseCode.SuccessWithInfo,
                msg: [`离场${count - 1}条`],
            };
        } catch (err) {
            await queryRunner.rollbackTransaction();

            throw err;
        } finally {
            await queryRunner.release();
        }
    }
    async createCntrLease(
        cntrLeaseObj: CntrLeaseDto,
        q?: QueryRunner,
    ): Promise<number> {
        const queryRunner = q ?? this.datasource.createQueryRunner();

        const errors = await validate(
            plainToInstance(CntrLeaseDto, cntrLeaseObj),
        );
        if (errors.length > 0) {
            throw new Error(Object.values(errors[0].constraints).join('   '));
        }

        const insertCntrResult = await queryRunner.manager
            .createQueryBuilder()
            .insert()
            .into(CntrLease)
            .values(cntrLeaseObj)
            .execute();

        if (insertCntrResult.raw.affectedRows === 1) {
            return insertCntrResult.raw.insertId;
        } else {
            return 0;
        }
    }
    async updateCntrLease(
        dto: UpdateLeaseCntrDto,
        q?: QueryRunner,
    ): Promise<ResponseJson<null, null>> {
        const queryRunner = q ?? this.datasource.createQueryRunner();
        const errors = await validate(plainToInstance(UpdateLeaseCntrDto, dto));
        if (errors.length > 0) {
            throw new Error(Object.values(errors[0].constraints).join('   '));
        }
        const updateCntrResult = await queryRunner.manager
            .createQueryBuilder()
            .update(CntrLease)
            .set(omit(dto, ['id']))
            .where('id = :id', { id: dto.id })
            .execute();
        if (updateCntrResult.affected != 1) {
            throw new Error(`箱号修改失败`);
        }

        return {
            code: ResponseCode.SuccessWithInfo,
            msg: ['修改成功'],
        };
    }
    async updateCntrLeaseRate(dto: CntrLeaseRateDto): Promise<ResponseJson<null, null>> {
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            for (let index = 0; index < dto.ids.length; index++) {
                const id = dto.ids[index];
                await queryRunner.manager
                    .createQueryBuilder()
                    .update(CntrLease)
                    .set({
                        day_rate: dto.day_rate,
                        currency_cod: dto.currency_cod
                    })
                    .where('id = :id', { id: id })
                    .execute();
            }
            await queryRunner.commitTransaction();
            return {
                code: ResponseCode.SuccessWithInfo,
                msg: ['修改成功']
            }
        } catch(err) {
            await queryRunner.rollbackTransaction();
            throw err;
        } finally {
            await queryRunner.release();
        }
    }
    async cleanCntrLeaseRate(dto: DeleteDto): Promise<ResponseJson<null, null>> {
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            for (let index = 0; index < dto.ids.length; index++) {
                const id = dto.ids[index];
                await queryRunner.manager
                    .createQueryBuilder()
                    .update(CntrLease)
                    .set({
                        day_rate: null,
                        currency_cod: null
                    })
                    .where('id = :id', { id: id })
                    .execute();
            }
            await queryRunner.commitTransaction();
            return {
                code: ResponseCode.SuccessWithInfo,
                msg: ['修改成功']
            }
        } catch(err) {
            await queryRunner.rollbackTransaction();
            throw err;
        } finally {
            await queryRunner.release();
        }
    }
    /**
     * 出租箱返场
     * @param dto
     * @param q
     * @returns
     */
    async BackCntrLease(
        dto: LeaseCntrBackDto,
        q?: QueryRunner,
    ): Promise<ResponseJson<null, null>> {
        const queryRunner = q ?? this.datasource.createQueryRunner();
        const errors = await validate(plainToInstance(LeaseCntrBackDto, dto));
        if (errors.length > 0) {
            throw new Error(Object.values(errors[0].constraints).join('   '));
        }
        const updateCntrResult = await queryRunner.manager
            .createQueryBuilder()
            .update(CntrLease)
            .set(omit(dto, ['id']))
            .where('id = :id', { id: dto.id })
            .execute();
        if (updateCntrResult.affected != 1) {
            throw new Error(`返场失败`);
        }

        return {
            code: ResponseCode.SuccessWithInfo,
            msg: ['返场成功'],
        };
    }
    async queryCntrLease(
        query: SqlQueryDto,
    ): Promise<ResponseJson<CntrLease[], number>> {
        query.left = [
            {
                property: 'cntr_lease.cntr',
                alias: 'cntr_file',
            },
        ];

        return await this.crud.query<CntrLease>(CntrLease, query);
    }
    async queryCntrLeaseDetail(
        query: SqlQueryDto,
    ): Promise<ResponseJson<VCntrLease[], number>> {
        return await this.crud.query<VCntrLease>(VCntrLease, query);
    }

    async queryCntrLeaseReport(
        dto: LeaseReportDto,
    ): Promise<ResponseJson<{ cntr_corp_cod: number, total: number, back_total: number, out_total: number }[], any>> {
        const startTim = formatDateTime(dto.start_tim);
        const endTim = formatDateTime(dto.end_tim);
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {

            const resultSql = queryRunner.manager
                .createQueryBuilder()
                .from(VCntrLease, 'v_cntr_lease')
                .select([
                    'v_cntr_lease.cntr_corp_cod as cntr_corp_cod',
                    'count(1) as total',
                    'sum(if(v_cntr_lease.back_date is null , 0, 1)) as back_total',
                    'sum(if(v_cntr_lease.back_date is null , 1, 0)) as out_total',
                ]).take(dto.take).skip(dto.skip)
                .where('v_cntr_lease.out_date between :start_date and :end_date', {
                    start_date: startTim,
                    end_date: endTim,
                })
                .groupBy('v_cntr_lease.cntr_corp_cod');
            const countSql = queryRunner.manager
                .createQueryBuilder()
                .from(VCntrLease, 'v_cntr_lease')
                .select(['cntr_corp_cod'])
                .where('v_cntr_lease.out_date between :start_date and :end_date', {
                    start_date: startTim,
                    end_date: endTim,
                })
                .groupBy('v_cntr_lease.cntr_corp_cod');

            const totalSql = await queryRunner.manager
                .createQueryBuilder()
                .from(VCntrLease, 'v_cntr_lease')
                .select([

                    'count(1) as total',
                    'sum(if(v_cntr_lease.back_date is null , 0, 1)) as back_total',
                    'sum(if(v_cntr_lease.back_date is null , 1, 0)) as out_total',
                ])
                .where('v_cntr_lease.out_date between :start_date and :end_date', {
                    start_date: startTim,
                    end_date: endTim,
                });

            if (dto.fee_owner) {
                resultSql.andWhere('v_cntr_lease.cntr_corp_cod = :cntr_corp_cod', { cntr_corp_cod: dto.fee_owner });
                totalSql.andWhere('v_cntr_lease.cntr_corp_cod = :cntr_corp_cod', { cntr_corp_cod: dto.fee_owner });
                countSql.andWhere('v_cntr_lease.cntr_corp_cod = :cntr_corp_cod', { cntr_corp_cod: dto.fee_owner });
            }
            const result = await resultSql.getRawMany();
            const totalResult = await totalSql.getRawOne();
            const countResult = await queryRunner.manager
            .createQueryBuilder()
            .select('count(1) as rowCount')
            .from("(" + countSql.getQuery() + ")", "subQuery")
            .setParameters(countSql.getParameters())
            .getRawOne();
            
            

            await queryRunner.commitTransaction();
            return {
                code: ResponseCode.SuccessNoMsg,
                msg: [''],
                obj: result,
                ext: {
                    rowCount: countResult.rowCount,
                    total: totalResult.total,
                    back_total: totalResult.back_total,
                    out_total: totalResult.out_total,
                }
            }
        } catch (err) {
            await queryRunner.rollbackTransaction();
            throw err;
        } finally {
            await queryRunner.release();
        }
    }

    async exportQueryCntrLease(
        query: SqlQueryDto,
    ): Promise<ResponseJson<string, null>> {
        return this.sharedService.exportQueryToExcel<VCntrLease>(
            VCntrLease,
            query,
            {},
        );
    }
    async exportQueryCntrLeasePlan(
        query: SqlQueryDto,
    ): Promise<ResponseJson<string, null>> {
        return this.sharedService.exportQueryToExcel<CntrLeasePlan>(
            CntrLeasePlan,
            query,
            {
                cntr_corp_cod: 'ClientKeyValue',
                cfs_cod: 'Cfs',
                cntr_owner: 'ClientKeyValue',
            },
        );
    }
    /**
     * 导入出租箱出场
     * update cntr_file set current_stat = '1' .
     * insert cntr_lease
     * @param file
     * @param planID
     * @param operNam
     * @returns
     */
    async importLeaseOutFromExcel(
        file: any,
        planID: number,
        operNam: string,
    ): Promise<ResponseJson<null, null>> {
        const workbook = XLSX.readFile(file.path, {
            type: 'binary',
            cellDates: true,
        });

        if (workbook.SheetNames.length === 0) {
            throw new Error('空文件');
        }
        const firstSheetName = workbook.SheetNames[0]; // 读取第一个工作表
        const sheet = workbook.Sheets[firstSheetName];
        const data = XLSX.utils.sheet_to_json(sheet, {
            header: 1,
            raw: false,
            dateNF: 'YYYY-MM-DD HH:mm:ss',
        }); // 将工作表转换为JSON

        if (data.length === 0) {
            throw new Error('空文件');
        }
        const count = data.length;
        let insertNum = 0;
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            const findPlan = await queryRunner.manager
                .createQueryBuilder<CntrLeasePlan>(
                    CntrLeasePlan,
                    'v_truck_cntr_leaseplan',
                )
                .select([
                    'v_truck_cntr_leaseplan.cntr_siz_cod',
                    'v_truck_cntr_leaseplan.cntr_typ_cod',
                    'v_truck_cntr_leaseplan.cntr_owner',
                    'v_truck_cntr_leaseplan.dam_id',
                    'v_truck_cntr_leaseplan.cfs_cod',
                    'v_truck_cntr_leaseplan.cntr_num',
                    'v_truck_cntr_leaseplan.cntr_work_num',
                ])
                .leftJoinAndSelect(
                    'v_truck_cntr_leaseplan.fees',
                    'truck_cntr_plan_fee',
                )
                .where('v_truck_cntr_leaseplan.id = :id', { id: planID })
                .getOne();
            const maxCntrNum =
                findPlan.cntr_num - (findPlan.cntr_work_num ?? 0);
            /* if (findPlan.cntr_num - (findPlan.cntr_work_num ?? 0) < count - 1) {
                throw new Error('超过计划剩余箱量');
            } */
            for (let index = 1; index < count; index++) {
                const row = data[index];
                if (isNil(row[0]?.toString())) {
                    continue;
                }
                if (insertNum >= maxCntrNum) {
                    throw new Error('超过计划剩余箱量');
                }
                const leaseCntr = {} as unknown as CntrLeaseDto;
                const feedetObj = {} as unknown as FeedetDto;
                const findCntr = await queryRunner.manager
                    .createQueryBuilder<Cntr>(Cntr, 'cntr_file')
                    .select([
                        'cntr_file.id',
                        'cntr_file.cntr',
                        'cntr_file.cntr_siz_cod',
                        'cntr_file.cntr_typ_cod',
                        'cntr_file.cntr_owner',
                        'cntr_file.dam_id',
                        'cntr_file.cfs_cod',
                    ])
                    .where("cntr = :cntr and current_stat = '2'", {
                        cntr: row[0]?.toString().trim().toUpperCase(),
                    })
                    .getOne();

                if (findCntr) {
                    if (findCntr.cntr_siz_cod !== findPlan.cntr_siz_cod) {
                        throw new Error(`箱号${findCntr.cntr}尺寸与计划不一致`);
                    }
                    if (findCntr.cntr_typ_cod !== findPlan.cntr_typ_cod) {
                        throw new Error(`箱号${findCntr.cntr}箱型与计划不一致`);
                    }
                    if (findCntr.cntr_owner !== findPlan.cntr_owner) {
                        throw new Error(
                            `箱号${findCntr.cntr}成本中心与计划不一致`,
                        );
                    }
                    if (findCntr.dam_id !== findPlan.dam_id) {
                        throw new Error(`箱号${findCntr.cntr}箱况与计划不一致`);
                    }
                    if (findCntr.cfs_cod !== findPlan.cfs_cod) {
                        throw new Error(`箱号${findCntr.cntr}站点与计划不一致`);
                    }
                } else {
                    throw new Error(
                        `箱号${row[0]?.toString().trim().toUpperCase()}不在场`,
                    );
                }
                leaseCntr.cntr_no = findCntr.id;
                leaseCntr.lease_sn = planID;
                leaseCntr.out_date = new Date(row[1]?.toString().trim());
                if (!leaseCntr.out_date) {
                    throw new Error(`箱号${findCntr.cntr}放箱日期错误`);
                }
                leaseCntr.out_truck_no_c = row[2]?.toString().trim();
                leaseCntr.out_driver_tel = row[3]?.toString().trim();
                await this.updateCntr(
                    { id: findCntr.id, current_stat: '1' },
                    queryRunner,
                );
                await this.createCntrLease(leaseCntr, queryRunner);
                insertNum++;
                for (let index = 0; index < findPlan.fees.length; index++) {
                    const element = findPlan.fees[index];
                    // feedetObj.out_truck_sn = planID;
                    // 堆存费 租箱费
                    if (
                        element.fee_cod === '0108' ||
                        element.fee_cod === '0105'
                    ) {
                        continue;
                    }
                    feedetObj.cntr_no = findCntr.id;
                    feedetObj.fee_owner = element.fee_owner;
                    feedetObj.fee_typ_cod = element.fee_typ_cod;
                    feedetObj.fee_cod = element.fee_cod;
                    feedetObj.fee_unit_cod = element.fee_unit_cod;
                    feedetObj.amount = element.amount;
                    feedetObj.currency_cod = element.currency_cod;
                    feedetObj.fee_nam = operNam;
                    feedetObj.fee_tim = leaseCntr.out_date;
                    feedetObj.fee_in_out = element.fee_in_out;
                    feedetObj.bill_no = findPlan.bill_no;
                    feedetObj.lease_sn = planID;
                    await this.businessService.createFeedet(
                        feedetObj,
                        queryRunner,
                    );
                }
                // await this.updateCntr(cntrObj, queryRunner);
            }
            await queryRunner.manager
                .createQueryBuilder()
                .update(CntrLeasePlan)
                .set({
                    cntr_work_num: (findPlan.cntr_work_num ?? 0) + insertNum,
                    finish_id:
                        (findPlan.cntr_work_num ?? 0) + insertNum >=
                            findPlan.cntr_num
                            ? 'Y'
                            : 'N',
                })
                .where('id = :id', { id: planID })
                .execute();
            await queryRunner.commitTransaction();
            return {
                code: ResponseCode.SuccessWithInfo,
                msg: [`离场${count - 1}条`],
            };
        } catch (err) {
            await queryRunner.rollbackTransaction();

            throw err;
        } finally {
            await queryRunner.release();
        }
    }
    async importLeaseBackFromExcel(
        file: any,
        planID: number,
        operNam: string,
    ): Promise<ResponseJson<null, null>> {
        const workbook = XLSX.readFile(file.path, {
            type: 'binary',
            cellDates: true,
        });

        if (workbook.SheetNames.length === 0) {
            throw new Error('空文件');
        }
        const firstSheetName = workbook.SheetNames[0]; // 读取第一个工作表
        const sheet = workbook.Sheets[firstSheetName];
        const data = XLSX.utils.sheet_to_json(sheet, {
            header: 1,
            raw: false,
            dateNF: 'YYYY-MM-DD HH:mm:ss',
        }); // 将工作表转换为JSON

        if (data.length === 0) {
            throw new Error('空文件');
        }
        const count = data.length;
        let insertNum = 0;
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            for (let index = 1; index < count; index++) {
                const row = data[index];
                if (isNil(row[0]?.toString())) {
                    continue;
                }
                const leaseCntr = {} as unknown as LeaseCntrBackDto;

                const findCntr = await queryRunner.manager
                    .createQueryBuilder<Cntr>(Cntr, 'cntr_file')
                    .select([
                        'cntr_file.id',
                        'cntr_file.cntr',
                        'cntr_file.cntr_siz_cod',
                        'cntr_file.cntr_typ_cod',
                        'cntr_file.cntr_owner',
                        'cntr_file.dam_id',
                        'cntr_file.cfs_cod',
                    ])
                    .where("cntr = :cntr and current_stat = '1'", {
                        cntr: row[0]?.toString().trim().toUpperCase(),
                    })
                    .getOne();

                if (findCntr) {
                    const back_date = new Date(row[1]?.toString().trim());
                    if (!back_date) {
                        throw new Error(`箱号${findCntr.cntr}返场日期格式错误`);
                    }
                    const findLeaseCntr = await queryRunner.manager
                        .createQueryBuilder<CntrLease>(CntrLease, 'cntr_lease')
                        .select([
                            'cntr_lease.id',
                            'cntr_lease.cntr_no',
                            'cntr_lease.out_date',
                        ])
                        .where(
                            'cntr_lease.lease_sn = :lease_sn and cntr_lease.cntr_no = :cntr_no',
                            {
                                lease_sn: planID,
                                cntr_no: findCntr.id,
                            },
                        )
                        .getOne();
                    if (findLeaseCntr) {
                        if (findLeaseCntr.out_date > back_date) {
                            throw new Error(
                                `箱号${findCntr.cntr}返场日期早于出租日期`,
                            );
                        }
                    } else {
                        throw new Error(
                            `箱号${row[0]
                                ?.toString()
                                .trim()
                                .toUpperCase()}不在计划内,不能返场`,
                        );
                    }
                    leaseCntr.id = findLeaseCntr.id;
                    leaseCntr.back_date = back_date;
                    await this.updateCntr(
                        { id: findCntr.id, current_stat: '2' },
                        queryRunner,
                    );
                    await this.BackCntrLease(leaseCntr, queryRunner);
                    insertNum++;
                } else {
                    throw new Error(
                        `箱号${row[0]
                            ?.toString()
                            .trim()
                            .toUpperCase()}不是离场箱`,
                    );
                }
            }
            await queryRunner.commitTransaction();
            return {
                code: ResponseCode.SuccessWithInfo,
                msg: [`返场${insertNum}条`],
            };
        } catch (err) {
            await queryRunner.rollbackTransaction();
            // console.log(err);
            throw err;
        } finally {
            await queryRunner.release();
        }
    }

    async inventoryCheck(
        file: any,
        cfs_cod: number,
    ): Promise<ResponseJson<any, null>> {
        const workbook = XLSX.readFile(file.path, {
            type: 'binary',
            cellDates: true,
        });

        if (workbook.SheetNames.length === 0) {
            throw new Error('空文件');
        }
        const firstSheetName = workbook.SheetNames[0]; // 读取第一个工作表
        const sheet = workbook.Sheets[firstSheetName];
        const data = XLSX.utils.sheet_to_json(sheet, {
            header: 1,
            raw: false,
            dateNF: 'YYYY-MM-DD HH:mm:ss',
        }); // 将工作表转换为JSON
        if (data.length === 0) {
            throw new Error('空文件');
        }
        const count = data.length;
        const result: { cntr: string; detail: string }[] = [];

        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            const cfss = await queryRunner.manager
                .createQueryBuilder<CyCfs>(CyCfs, 'c_cy_cfs')
                .select(['id'])
                .where('parentId = :parentId', { parentId: cfs_cod })
                .orWhere('id = :id', { id: cfs_cod })
                .execute();

            const cfsarray = [];
            cfss.forEach((element) => {
                cfsarray.push(element.id);
            });

            const cntrs = await queryRunner.manager
                .createQueryBuilder<VInventory>(VInventory, 'v_inventory')
                .select(['cntr', 'cntr_siz_cod', 'cntr_typ_cod', 'to_port_tim'])
                .where('cfs_cod in (:...cfs_cods)', { cfs_cods: cfsarray })
                .execute();

            forEachRight(data, (row, index) => {
                // 1:箱号 2:尺寸  4:箱型  6:箱况 7: 进场时间

                if (row[1] !== '箱号' && !isNil(row[1])) {
                    const i = cntrs.findIndex((cntr) => {
                        return cntr.cntr === row[1].trim().toUpperCase();
                    });
                    if (i >= 0) {
                        let msg = '';
                        if (
                            cntrs[i].cntr_siz_cod !==
                            row[2].trim().toUpperCase()
                        ) {
                            msg += '尺寸不一致  ';
                        }
                        if (
                            cntrs[i].cntr_typ_cod !==
                            row[4].trim().toUpperCase()
                        ) {
                            msg += '箱型不一致  ';
                        }
                        if (
                            cntrs[i].to_port_tim.setHours(0, 0, 0) !==
                            new Date(row[7]?.toString().trim()).setHours(
                                0,
                                0,
                                0,
                            )
                        ) {
                            msg += '进场日期不一致  ';
                        }
                        if (msg.length > 0) {
                            result.push({
                                cntr: cntrs[i]['cntr'],
                                detail: msg,
                            });
                        }
                        cntrs.splice(i, 1);
                    } else {
                        result.push({
                            cntr: row[1],
                            detail: '非在场箱',
                        });
                    }
                }
            });
            cntrs.forEach((cntr) => {
                result.push({
                    cntr: cntr.cntr,
                    detail: '报表中无此箱',
                });
            });
            await queryRunner.commitTransaction();
            return {
                code: ResponseCode.SuccessWithInfo,
                msg: [`比对${count - 1}条数据`],
                obj: result,
            };
        } catch (err) {
            await queryRunner.rollbackTransaction();
            // console.log(err);
            throw err;
        } finally {
            await queryRunner.release();
        }
    }
    async queryInventory(
        query: SqlQueryDto,
    ): Promise<ResponseJson<VInventory[], number>> {
        return await this.crud.query<VInventory>(VInventory, query);
    }

    async exportQueryInventory(
        query: SqlQueryDto,
    ): Promise<ResponseJson<string, null>> {
        return await this.sharedService.exportQueryToExcel<VInventory>(
            VInventory,
            query,
            {
                cfs_cod: 'Cfs',
            },
        );
    }

    async queryInventoryGroup(
        query: SqlQueryDto,
    ): Promise<ResponseJson<VInventoryGroup[], number>> {

        return await this.crud.query<VInventoryGroup>(VInventoryGroup, query);
    }
    /**
     * 首页数据展示
     * @returns 
     */
    async caDisplay(): Promise<ResponseJson<any, null>> {
        const cntrStat = await this.datasource.createQueryBuilder<Cntr>(Cntr, 'cntr_file')
            .select(['current_stat as current_stat', 'count(1) as count'])
            .where("current_stat <> '0'")
            .groupBy('current_stat')
            .getRawMany();
        const cntrDam = await this.datasource.createQueryBuilder<Cntr>(Cntr, 'cntr_file')
            .select(['dam_id as dam_id', 'count(1) as count'])
            .where("current_stat <> '0'")
            .groupBy('dam_id')
            .getRawMany();
        const cntrCfs = await this.datasource.createQueryBuilder<Cntr>(Cntr, 'cntr_file')
            .select(['cfs_cod as cfs_cod', 'count(1) as count'])
            .where("current_stat <> '0'")
            .andWhere('cfs_cod is not null')
            .groupBy('cfs_cod')
            .orderBy('count', 'DESC')
            .getRawMany();

        return {
            code: ResponseCode.SuccessNoMsg,
            obj: {
                cntrStat,
                cntrDam,
                cntrCfs
            }
        };
    }
    /**
     * 创建租箱计划
     */
    async createLeasePlan(
        dto: LeasePlanDto,
    ): Promise<ResponseJson<CntrLeasePlan, null>> {
        const fees = dto.fees;
        const plan = omit(dto, ['fees']);
        plan.carry_id = 'S';
        plan.finish_id = 'N';
        await checkDto(LeasePlanDto, plan);
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            const insertPlanResult = await queryRunner.manager
                .createQueryBuilder()
                .insert()
                .into(CntrLeasePlan)
                .values(plan)
                .execute();
            const plan_id = insertPlanResult.identifiers[0]['id'];

            if (!isNil(fees)) {
                fees.forEach((fee) => {
                    fee.truck_sn = plan_id;
                });

                await queryRunner.manager
                    .createQueryBuilder()
                    .insert()
                    .into(CntrPlanFee)
                    .values(fees)
                    .execute();
            }

            const newPlan = await queryRunner.manager
                .createQueryBuilder<CntrLeasePlan>(
                    CntrLeasePlan,
                    'v_truck_cntr_leaseplan',
                )
                .leftJoinAndSelect(
                    'v_truck_cntr_leaseplan.fees',
                    'truck_cntr_plan_fee',
                )
                .where('v_truck_cntr_leaseplan.id = :id', { id: plan_id })
                .getOne();
            await queryRunner.commitTransaction();
            return {
                code: ResponseCode.SuccessWithInfo,
                obj: newPlan,
                msg: ['创建成功'],
            };
        } catch (err) {
            await queryRunner.rollbackTransaction();
            throw err;
        } finally {
            await queryRunner.release();
        }
    }
    async updateLeasePlan(
        dto: UpdateLeasePlanDto,
    ): Promise<ResponseJson<CntrLeasePlan, null>> {
        const fees = dto.fees as unknown[] as UpdatePlanFeeDto[];
        const plan = omit(dto, ['fees']);
        await checkDto(UpdateLeasePlanDto, plan);
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            const dbPlan = await queryRunner.manager.createQueryBuilder<CntrPlan>(CntrPlan, 'truck_cntr_plan')
                .select('cntr_work_num', 'cntr_work_num')
                .where('id = :id', { id: dto['id'] })
                .getRawOne();
            plan.finish_id = (plan.cntr_num - (dbPlan?.cntr_work_num ?? 0) > 0) ? 'N' : 'Y';
            const updatePlanResult = await queryRunner.manager
                .createQueryBuilder()
                .update(CntrLeasePlan)
                .set(plan)
                .where('id = :id', { id: dto['id'] })
                .execute();
            if (updatePlanResult.affected != 1) {
                throw new Error('无此计划，修改失败');
            }
            /* await queryRunner.manager.createQueryBuilder().delete().from(CntrPlanFee)
                .where('truck_sn = :id', { id: dto['id'] }).execute(); */
            if (!isNil(fees)) {
                const feeIds = []; // 已存在需保留的费用
                fees.forEach((fee) => {
                    fee.truck_sn = dto['id'];
                    if (!isNil(fee['id'])) {
                        feeIds.push(fee.id);
                    }
                });
                // 删除不需要的费用
                if (feeIds.length > 0) {
                    await queryRunner.manager
                        .createQueryBuilder()
                        .delete()
                        .from(CntrPlanFee)
                        .where('truck_sn = :truck_sn', { truck_sn: dto['id'] })
                        .andWhere('id not in (:...id)', { id: feeIds })
                        .execute();
                } else {
                    await queryRunner.manager
                        .createQueryBuilder()
                        .delete()
                        .from(CntrPlanFee)
                        .where('truck_sn = :truck_sn', { truck_sn: dto['id'] })
                        .execute();
                }

                fees.forEach(async (element) => {
                    if (isNil(element['id'])) {
                        await queryRunner.manager
                            .createQueryBuilder()
                            .insert()
                            .into(CntrPlanFee)
                            .values(element)
                            .execute();
                    } else {
                        const updateObj = omit(element, ['id', 'isEdit']);
                        // console.log('updateObj', updateObj);

                        await queryRunner.manager
                            .createQueryBuilder()
                            .update(CntrPlanFee)
                            .set(updateObj)
                            .where('id = :id', { id: element.id })
                            .execute();
                    }
                });
            }

            await queryRunner.commitTransaction();
            const newPlan = await this.datasource
                .createQueryBuilder<CntrLeasePlan>(
                    CntrLeasePlan,
                    'v_truck_cntr_leaseplan',
                )
                .leftJoinAndSelect(
                    'v_truck_cntr_leaseplan.fees',
                    'truck_cntr_plan_fee',
                )
                .where('v_truck_cntr_leaseplan.id = :id', { id: dto['id'] })
                .getOne();

            // console.log('newPlan', newPlan);

            return {
                code: ResponseCode.SuccessWithInfo,
                obj: newPlan,
                msg: ['修改成功'],
            };
        } catch (err) {
            await queryRunner.rollbackTransaction();
            // console.log(err);

            throw err;
        } finally {
            await queryRunner.release();
        }
        // return await this.crud.update<Client>(Client, dto);
    }
    async deleteLeaseCntrPlan(dto: DeleteDto): Promise<ResponseJson> {
        return await this.crud.delete<CntrLeasePlan>(CntrLeasePlan, dto);
    }
    async queryCntrLeasePlan(
        query: SqlQueryDto,
    ): Promise<ResponseJson<CntrLeasePlan[], number>> {
        query.left = [
            /*  {
                property: 'truck_cntr_plan.cntrs',
                alias: 'truck_cntr_plan_cntr',
            }, */
            {
                property: 'v_truck_cntr_leaseplan.fees',
                alias: 'truck_cntr_plan_fee',
            },
        ];
        return await this.crud.query<CntrLeasePlan>(CntrLeasePlan, query);
    }
    /**
     * 创建退租计划
     */
    async createRentPlan(
        dto: RentPlanDto,
    ): Promise<ResponseJson<CntrRentPlan, null>> {
        const fees = dto.fees;
        const plan = omit(dto, ['fees']);
        plan.carry_id = 'E';
        // plan.finish_id = 'N';
        await checkDto(RentPlanDto, plan);
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            const insertPlanResult = await queryRunner.manager
                .createQueryBuilder()
                .insert()
                .into(CntrRentPlan)
                .values(plan)
                .execute();
            const plan_id = insertPlanResult.identifiers[0]['id'];

            if (!isNil(fees)) {
                fees.forEach((fee) => {
                    fee.truck_sn = plan_id;
                });

                await queryRunner.manager
                    .createQueryBuilder()
                    .insert()
                    .into(CntrPlanFee)
                    .values(fees)
                    .execute();
            }

            const newPlan = await queryRunner.manager
                .createQueryBuilder<CntrRentPlan>(
                    CntrRentPlan,
                    'v_truck_cntr_rentplan',
                )
                .leftJoinAndSelect(
                    'v_truck_cntr_rentplan.fees',
                    'truck_cntr_plan_fee',
                )
                .where('v_truck_cntr_rentplan.id = :id', { id: plan_id })
                .getOne();
            await queryRunner.commitTransaction();
            return {
                code: ResponseCode.SuccessWithInfo,
                obj: newPlan,
                msg: ['创建成功'],
            };
        } catch (err) {
            await queryRunner.rollbackTransaction();
            throw err;
        } finally {
            await queryRunner.release();
        }
    }
    async updateRentPlan(
        dto: UpdateRentPlanDto,
    ): Promise<ResponseJson<CntrRentPlan, null>> {
        const fees = dto.fees as unknown[] as UpdatePlanFeeDto[];
        const plan = omit(dto, ['fees']);
        await checkDto(UpdateRentPlanDto, plan);
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            const dbPlan = await queryRunner.manager.createQueryBuilder<CntrPlan>(CntrPlan, 'truck_cntr_plan')
                .select('cntr_work_num', 'cntr_work_num')
                .where('id = :id', { id: dto['id'] })
                .getRawOne();
            plan.finish_id = (plan.cntr_num - (dbPlan?.cntr_work_num ?? 0) > 0) ? 'N' : 'Y';
            const updatePlanResult = await queryRunner.manager
                .createQueryBuilder()
                .update(CntrRentPlan)
                .set(plan)
                .where('id = :id', { id: dto['id'] })
                .execute();
            if (updatePlanResult.affected != 1) {
                throw new Error('无此计划，修改失败');
            }
            /* await queryRunner.manager.createQueryBuilder().delete().from(CntrPlanFee)
                .where('truck_sn = :id', { id: dto['id'] }).execute(); */
            if (!isNil(fees)) {
                const feeIds = []; // 已存在需保留的费用
                fees.forEach((fee) => {
                    fee.truck_sn = dto['id'];
                    if (!isNil(fee['id'])) {
                        feeIds.push(fee.id);
                    }
                });
                // 删除不需要的费用
                if (feeIds.length > 0) {
                    await queryRunner.manager
                        .createQueryBuilder()
                        .delete()
                        .from(CntrPlanFee)
                        .where('truck_sn = :truck_sn', { truck_sn: dto['id'] })
                        .andWhere('id not in (:...id)', { id: feeIds })
                        .execute();
                } else {
                    await queryRunner.manager
                        .createQueryBuilder()
                        .delete()
                        .from(CntrPlanFee)
                        .where('truck_sn = :truck_sn', { truck_sn: dto['id'] })
                        .execute();
                }

                fees.forEach(async (element) => {
                    if (isNil(element['id'])) {
                        await queryRunner.manager
                            .createQueryBuilder()
                            .insert()
                            .into(CntrPlanFee)
                            .values(element)
                            .execute();
                    } else {
                        const updateObj = omit(element, ['id', 'isEdit']);
                        // console.log('updateObj', updateObj);

                        await queryRunner.manager
                            .createQueryBuilder()
                            .update(CntrPlanFee)
                            .set(updateObj)
                            .where('id = :id', { id: element.id })
                            .execute();
                    }
                });
            }

            await queryRunner.commitTransaction();
            const newPlan = await this.datasource
                .createQueryBuilder<CntrRentPlan>(
                    CntrRentPlan,
                    'v_truck_cntr_rentplan',
                )
                .leftJoinAndSelect(
                    'v_truck_cntr_rentplan.fees',
                    'truck_cntr_plan_fee',
                )
                .where('v_truck_cntr_rentplan.id = :id', { id: dto['id'] })
                .getOne();

            // console.log('newPlan', newPlan);

            return {
                code: ResponseCode.SuccessWithInfo,
                obj: newPlan,
                msg: ['修改成功'],
            };
        } catch (err) {
            await queryRunner.rollbackTransaction();
            // console.log(err);

            throw err;
        } finally {
            await queryRunner.release();
        }
        // return await this.crud.update<Client>(Client, dto);
    }
    async deleteRentCntrPlan(dto: DeleteDto): Promise<ResponseJson> {
        return await this.crud.delete<CntrRentPlan>(CntrRentPlan, dto);
    }
    async queryCntrRentPlan(
        query: SqlQueryDto,
    ): Promise<ResponseJson<CntrRentPlan[], number>> {
        query.left = [
            /*  {
                property: 'truck_cntr_plan.cntrs',
                alias: 'truck_cntr_plan_cntr',
            }, */
            {
                property: 'v_truck_cntr_rentplan.fees',
                alias: 'truck_cntr_plan_fee',
            },
        ];
        return await this.crud.query<CntrRentPlan>(CntrRentPlan, query);
    }
    async quueryReleaseForm(
        query: SqlQueryDto,
    ): Promise<ResponseJson<LeaseForm[], number>> {
        return await this.crud.query<LeaseForm>(LeaseForm, query);
    }
    /**
     * 取消出租箱返场
     * @param dto
     * @returns
     */
    async cancelLeaseCntrBack(dto: DeleteDto): Promise<ResponseJson> {
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            for (let index = 0; index < dto.ids.length; index++) {
                const leaseID = dto.ids[index];
                const leaseCntr = await queryRunner.manager
                    .createQueryBuilder<CntrLease>(CntrLease, 'cntr_lease')
                    .leftJoinAndSelect('cntr_lease.cntr', 'cntr_file')
                    .where('cntr_lease.id = :id', { id: leaseID })
                    .getOne();
                if (leaseCntr) {
                    if (leaseCntr.cntr.current_stat !== '2') {
                        throw new Error('非在场箱，撤销失败');
                    }
                    await this.updateCntr(
                        { id: leaseCntr.cntr.id, current_stat: '1' },
                        queryRunner,
                    );
                    await queryRunner.manager
                        .createQueryBuilder()
                        .update(CntrLease)
                        .set({ back_date: null })
                        .where('id = :id', { id: leaseID })
                        .execute();
                } else {
                    throw new Error('出租箱错误，撤销失败');
                }
            }
            await queryRunner.commitTransaction();
            return {
                code: ResponseCode.SuccessWithInfo,
                msg: ['撤销成功'],
            };
        } catch (err) {
            await queryRunner.rollbackTransaction();
            // console.log(err);

            throw err;
        } finally {
            await queryRunner.release();
        }
    }
    /**
     * 取消出租箱出场
     * @param dto
     * @returns
     */
    async cancelLeaseCntrOut(dto: DeleteDto): Promise<ResponseJson> {
        let num = 0;
        let planID = null;
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            for (let index = 0; index < dto.ids.length; index++) {
                const leaseID = dto.ids[index];
                const leaseCntr = await queryRunner.manager
                    .createQueryBuilder<CntrLease>(CntrLease, 'cntr_lease')
                    .leftJoinAndSelect('cntr_lease.cntr', 'cntr_file')
                    .where('cntr_lease.id = :id', { id: leaseID })
                    .getOne();
                if (leaseCntr) {
                    if (leaseCntr.cntr.current_stat !== '1') {
                        throw new Error('非出租箱，撤销失败');
                    }
                    if (leaseCntr.out_date) {
                        // 删除费用
                        await queryRunner.manager
                            .createQueryBuilder()
                            .delete()
                            .from(FeeDet)
                            .where(
                                'cntr_no = :cntr_no and lease_sn = :lease_sn and fee_tim = :fee_tim',
                                {
                                    cntr_no: leaseCntr.cntr_no,
                                    lease_sn: leaseCntr.lease_sn,
                                    fee_tim: leaseCntr.out_date,
                                },
                            )
                            .execute();
                        // 删除cntr_lease
                        await queryRunner.manager
                            .createQueryBuilder()
                            .delete()
                            .from(CntrLease)
                            .where('id = :id', { id: leaseID })
                            .execute();
                        // 修改cntr_file
                        await this.updateCntr(
                            { id: leaseCntr.cntr_no, current_stat: '2' },
                            queryRunner,
                        );
                        // 修改truck_cntr_plan
                        num++;
                    } else {
                        throw new Error(
                            `箱号${leaseCntr.cntr.cntr}出租时间错误，撤销失败`,
                        );
                    }
                } else {
                    throw new Error('出租箱错误，撤销失败');
                }
                planID = leaseCntr.lease_sn;
            }
            const plan = await queryRunner.manager
                .createQueryBuilder<CntrPlan>(CntrPlan, 'truck_cntr_plan')
                .select(['truck_cntr_plan.cntr_work_num'])
                .where('id = :id', { id: planID })
                .getOne();
            if (plan) {
                await queryRunner.manager
                    .createQueryBuilder()
                    .update(CntrPlan)
                    .set({
                        cntr_work_num: () => `cntr_work_num - ${num}`,
                        finish_id: 'N',
                    })
                    .where('id = :truck_sn', { truck_sn: planID })
                    .execute();
            } else {
                throw new Error('取出租箱计划错误，撤销失败');
            }

            await queryRunner.commitTransaction();
            return {
                code: ResponseCode.SuccessWithInfo,
                msg: ['撤销成功'],
            };
        } catch (err) {
            await queryRunner.rollbackTransaction();
            // console.log(err);

            throw err;
        } finally {
            await queryRunner.release();
        }
    }
    /**
     * 取消箱离场
     * @param dto
     * @returns
     */
    async cancelCntrOut(dto: DeleteDto): Promise<ResponseJson> {
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            for (let index = 0; index < dto.ids.length; index++) {
                const cntr_no = dto.ids[index];
                const cntr = await queryRunner.manager
                    .createQueryBuilder<Cntr>(Cntr, 'cntr_file')
                    .where('id = :id', { id: cntr_no })
                    .getOne();
                if (cntr) {
                    if (cntr.current_stat !== '0') {
                        throw new Error('非离场箱，撤销失败');
                    }
                    if (!cntr.out_truck_sn) {
                        throw new Error(
                            `箱${cntr.cntr}离场计划号错误，撤销失败`,
                        );
                    }
                    if (cntr.leav_port_tim) {
                        // 删除费用
                        await queryRunner.manager
                            .createQueryBuilder()
                            .delete()
                            .from(FeeDet)
                            .where(
                                'cntr_no = :cntr_no and fee_tim = :fee_tim ',
                                {
                                    cntr_no: cntr_no,
                                    fee_tim: cntr.leav_port_tim,
                                },
                            )
                            .execute();


                        // 修改cntr_file
                        await queryRunner.manager
                            .createQueryBuilder()
                            .update(Cntr)
                            .set({
                                out_truck_sn: null,
                                release_no: null,
                                cntr_corp_cod: null,
                                current_stat: '2',
                                allow_date: null,
                                leav_port_tim: null,
                                out_truck_no_c: null,
                                out_truck_tel: null,
                            })
                            .where('id = :id', { id: cntr_no })
                            .execute();

                        /* await this.updateCntr({
                            id: cntr_no,
                            out_truck_sn: null,
                            release_no: null,
                            cntr_corp_cod: null,
                            current_stat: '2',
                            allow_date: null,
                            leav_port_tim: null,
                            out_truck_no_c: null,
                            out_truck_tel: null
                        }, queryRunner); */
                        // 修改truck_cntr_plan
                        await queryRunner.manager
                            .createQueryBuilder()
                            .update(CntrPlan)
                            .set({
                                cntr_work_num: () => `cntr_work_num - 1`,
                                finish_id: 'N',
                            })
                            .where('id = :id', { id: cntr.out_truck_sn })
                            .execute();
                    } else {
                        throw new Error(
                            `箱号${cntr.cntr}离场时间错误，撤销失败`,
                        );
                    }
                } else {
                    throw new Error(`第${index + 1}个箱子错误，撤销失败`);
                }
            }
            await queryRunner.commitTransaction();
            return {
                code: ResponseCode.SuccessWithInfo,
                msg: ['撤销成功'],
            };
        } catch (err) {
            await queryRunner.rollbackTransaction();
            // console.log(err);

            throw err;
        } finally {
            await queryRunner.release();
        }
    }
    /**
     * 取消箱
     * @param dto
     * @returns
     */
    async cancelCntr(dto: DeleteDto): Promise<ResponseJson> {
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            for (let index = 0; index < dto.ids.length; index++) {
                const cntr_no = dto.ids[index];
                const cntr = await queryRunner.manager
                    .createQueryBuilder<Cntr>(Cntr, 'cntr_file')
                    .where('id = :id', { id: cntr_no })
                    .getOne();
                if (cntr) {
                    if (cntr.current_stat !== '2') {
                        throw new Error('非在场箱，撤销失败');
                    }
                    if (!cntr.in_truck_sn) {
                        throw new Error(
                            `箱${cntr.cntr}进场计划号错误，撤销失败`,
                        );
                    }
                    if (cntr.to_port_tim) {
                        // 删除费用
                        await queryRunner.manager
                            .createQueryBuilder()
                            .delete()
                            .from(FeeDet)
                            .where('cntr_no = :cntr_no', {
                                cntr_no: cntr_no,
                            })
                            .execute();

                        // 删除cntr_file
                        await queryRunner.manager
                            .createQueryBuilder()
                            .delete()
                            .from(Cntr)
                            .where('id = :id', { id: cntr_no })
                            .execute();
                        // 修改 truck_cntr_plan_cntr
                        await queryRunner.manager
                            .createQueryBuilder()
                            .update(CntrPlanCntr)
                            .set({
                                used_id: 'N'
                            })
                            .where('truck_sn = :truck_sn', { truck_sn: cntr.in_truck_sn })
                            .andWhere('cntr = :cntr', { cntr: cntr.cntr })
                            .execute();
                        // 修改truck_cntr_plan
                        await queryRunner.manager
                            .createQueryBuilder()
                            .update(CntrPlan)
                            .set({
                                cntr_work_num: () => `cntr_work_num - 1`,
                                finish_id: 'N',
                            })
                            .where('id = :id', { id: cntr.in_truck_sn })
                            .execute();
                    } else {
                        throw new Error(
                            `箱号${cntr.cntr}进场时间错误，撤销失败`,
                        );
                    }
                } else {
                    throw new Error(`箱号${cntr.cntr}错误，撤销失败`);
                }
            }
            await queryRunner.commitTransaction();
            return {
                code: ResponseCode.SuccessWithInfo,
                msg: ['撤销成功'],
            };
        } catch (err) {
            await queryRunner.rollbackTransaction();
            // console.log(err);

            throw err;
        } finally {
            await queryRunner.release();
        }
    }

    async busiEcharts(dto: BusiReportDto): Promise<ResponseJson<any, string[]>> {
        const startTim = dayjs(dto.start_tim).startOf('month');
        const endTim = dayjs(dto.end_tim).endOf('month');
        const months = getMonthsBetween(startTim.toDate(), endTim.toDate());

        const sqls: SelectQueryBuilder<any>[] = [];
        // 购入
        const inCntrSql = this.datasource.createQueryBuilder<Cntr>(Cntr, 'cntr_file')
            .select(["date_format(to_port_tim, '%Y%m') as month", "count(*) as value"])
            .where('to_port_tim between :start_tim and :end_tim', {
                start_tim: startTim.toDate(), end_tim: endTim.toDate()
            })
            .orderBy('month')
            // .groupBy("date_format(to_port_tim, '%Y%m')");
            .groupBy('month');
        sqls.push(inCntrSql);
        // 出售
        const outCntrSql = this.datasource.createQueryBuilder<Cntr>(Cntr, 'cntr_file')
            .select(["date_format(leav_port_tim, '%Y%m') as month", "count(*) as value"])
            .where('leav_port_tim between :start_tim and :end_tim', {
                start_tim: startTim.toDate(), end_tim: endTim.toDate()
            })
            .orderBy('month')
            // .groupBy("date_format(to_port_tim, '%Y%m')");
            .groupBy('month');
        sqls.push(outCntrSql);

        if (!isNil(dto.fee_owner)) {
            for (let index = 0; index < sqls.length; index++) {
                const element = sqls[index];
                element.andWhere('cntr_owner = :cntr_owner', { cntr_owner: dto.fee_owner });
            }
        }
        const inCntr = await inCntrSql.getRawMany();
        const outCntr = await outCntrSql.getRawMany();

        // 堆存
        const stackCntr: {
            month: string,
            value: string
        }[] = [];
        // 出租
        const leaseCntr: {
            month: string,
            value: string
        }[] = [];
        for (let index = 0; index < months.length; index++) {
            let value = 0;
            let leaseValue = 0;
            const stackStartTim = dayjs(`${months[index]}01`, 'YYYYMMDD').startOf('month');
            const stackEndTim = dayjs(`${months[index]}01`, 'YYYYMMDD').endOf('month');
            const stackCntrSql = await this.datasource.createQueryBuilder<Cntr>(Cntr, 'cntr_file')
                .select(['id', 'cntr', 'cntr_siz_cod', 'cntr_typ_cod', 'cntr_owner'])
                .where('to_port_tim <= :endTim', { endTim: stackEndTim.toDate() })
                .andWhere(
                    new Brackets((qb) => {
                        qb.where('leav_port_tim >= :startTim', { startTim: stackStartTim.toDate() })
                            .orWhere('leav_port_tim is null')
                    }),
                );
            if (!isNil(dto.fee_owner)) {
                stackCntrSql.andWhere('cntr_owner = :cntr_owner', { cntr_owner: dto.fee_owner });
            }
            const stackCntrs = await stackCntrSql.getRawMany();
            for (let cntrIndex = 0; cntrIndex < stackCntrs.length; cntrIndex++) {
                const cntr = stackCntrs[cntrIndex];

                const calObj: {
                    start?: dayjs.Dayjs,
                    end?: dayjs.Dayjs
                } = {};
                const leaseObj: {
                    start?: dayjs.Dayjs,
                    end?: dayjs.Dayjs
                } = {};
                // 堆存动态
                const cntrStats = await this.datasource.createQueryBuilder<PortCntrStat>(PortCntrStat, 'port_cntr_stat')
                    .select(['port_cntr_stat.current_stat as current_stat', 'port_cntr_stat.stat_tim as stat_tim'])
                    .where('cntr_no = :cntr_no', { cntr_no: cntr.id })
                    .orderBy('stat_tim', 'ASC')
                    .getRawMany();
                for (let statIndex = 0; statIndex < cntrStats.length; statIndex++) {
                    const stat = cntrStats[statIndex];
                    if (stat.current_stat === '2') {
                        if (isNil(calObj.start)) {
                            calObj.start = dayjs(stat.stat_tim);
                        }
                        if (dayjs(stat.stat_tim).isAfter(stackEndTim)) {  // 动态时间大于统计截止时间
                            // calObj.end = dayjs(endTim);
                            break;
                        }
                    }
                    if (stat.current_stat !== '2') {   // 计时截止
                        if (dayjs(stat.stat_tim).isBefore(stackStartTim)) { // 动态时间早于统计开始时间，在统计开始时间前已经离场
                            calObj.start = null;
                            calObj.end = null;
                        } else {
                            calObj.end = dayjs.min(dayjs(stat.stat_tim), stackEndTim);
                            if (!isNil(calObj.start)) {  // 排除场外多次动态的情况
                                calObj.start = dayjs.max(calObj.start, stackStartTim);
                                const days = calObj.end.startOf('day').diff(calObj.start.startOf('day'), 'day') + 1;
                                if (days > 0) {
                                    value += days;
                                }
                            }
                            calObj.start = null;
                        }
                    }
                }
                if (isNil(calObj.end)) {
                    calObj.end = dayjs(stackEndTim);
                }
                if (!isNil(calObj.start)) {
                    calObj.start = dayjs.max(calObj.start, stackStartTim);
                    const days = calObj.end.startOf('day').diff(calObj.start.startOf('day'), 'day') + 1;
                    if (days > 0) {
                        value += days;
                    }
                }
                // 出租动态
                const leaseStats = await this.datasource.createQueryBuilder<PortCntrStat>(PortCntrStat, 'port_cntr_stat')
                    .select(['port_cntr_stat.current_stat as current_stat', 'port_cntr_stat.cntr_stat_cod as cntr_stat_cod',
                        'port_cntr_stat.stat_tim as stat_tim'])
                    .where('cntr_no = :cntr_no', { cntr_no: cntr.id })
                    .andWhere(
                        new Brackets((qb) => {
                            qb.where('cntr_stat_cod = :stat_cod1', { stat_cod1: EOQZCC })
                                .orWhere('cntr_stat_cod = :stat_cod2', { stat_cod2: EITZJC })
                        }),
                    )
                    .orderBy('stat_tim', 'ASC')
                    .getRawMany();
                for (let statIndex = 0; statIndex < leaseStats.length; statIndex++) {
                    const stat = leaseStats[statIndex];
                    if (stat.current_stat === '1') {
                        leaseObj.start = dayjs(stat.stat_tim);
                        if (dayjs(stat.stat_tim).isAfter(dayjs(stackEndTim))) {  // 动态时间大于统计截止时间
                            break;
                        }
                    }
                    if (stat.current_stat === '2') {
                        if (dayjs(stat.stat_tim).isBefore(dayjs(stackStartTim))) { // 动态时间早于统计开始时间，在统计开始时间前已经返场
                            leaseObj.start = null;
                            leaseObj.end = null;
                        } else {
                            leaseObj.end = dayjs.min(dayjs(stat.stat_tim), dayjs(stackEndTim));
                            if (!isNil(leaseObj.start)) {
                                leaseObj.start = dayjs.max(leaseObj.start, dayjs(stackStartTim));
                                const days = leaseObj.end.startOf('day').diff(leaseObj.start.startOf('day'), 'day') + 1;
                                if (days > 0) {
                                    leaseValue += days;
                                }
                                leaseObj.end = null;
                            }
                            leaseObj.start = null;
                        }
                    }
                }
                if (isNil(leaseObj.end)) {
                    leaseObj.end = dayjs(stackEndTim);
                }
                if (!isNil(leaseObj.start)) {
                    leaseObj.start = dayjs.max(leaseObj.start, dayjs(stackStartTim));
                    const days = leaseObj.end.startOf('day').diff(leaseObj.start.startOf('day'), 'day') + 1;
                    if (days > 0) {
                        leaseValue += days;
                    }
                    leaseObj.end = null;
                }

            }
            stackCntr.push({
                month: months[index], value: value.toString()
            })
            leaseCntr.push({
                month: months[index], value: leaseValue.toString()
            })
        }

        /* for (let index = 0; index < months.length; index++) {
            let value = 0;
            const stackStartTim = dayjs(`${months[index]}01`, 'YYYYMMDD').startOf('month');
            const stackEndTim = dayjs(`${months[index]}01`, 'YYYYMMDD').endOf('month');
            const stackCntrSql = await this.datasource.createQueryBuilder<Cntr>(Cntr, 'cntr_file')
                .select(['id', 'cntr', 'cntr_siz_cod', 'cntr_typ_cod', 'cntr_owner'])
                .where('to_port_tim <= :endTim', { endTim: stackEndTim.toDate() })
                .andWhere(
                    new Brackets((qb) => {
                        qb.where('leav_port_tim >= :startTim', { startTim: stackStartTim.toDate() })
                            .orWhere('leav_port_tim is null')
                    }),
                );
            if (!isNil(dto.fee_owner)) {
                stackCntrSql.andWhere('cntr_owner = :cntr_owner', { cntr_owner: dto.fee_owner });
            }
            const stackCntrs = await stackCntrSql.getRawMany();
            for (let cntrIndex = 0; cntrIndex < stackCntrs.length; cntrIndex++) {
                const cntr = stackCntrs[cntrIndex];
                const calObj: {
                    start?: dayjs.Dayjs,
                    end?: dayjs.Dayjs
                } = {};
                const cntrStats = await this.datasource.createQueryBuilder<PortCntrStat>(PortCntrStat, 'port_cntr_stat')
                .select(['port_cntr_stat.current_stat as current_stat', 'port_cntr_stat.cntr_stat_cod as cntr_stat_cod',
                    'port_cntr_stat.stat_tim as stat_tim'])
                .where('cntr_no = :cntr_no', { cntr_no: cntr.id })
                .andWhere(
                    new Brackets((qb) => {
                        qb.where('cntr_stat_cod = :stat_cod1', { stat_cod1: EOQZCC })
                            .orWhere('cntr_stat_cod = :stat_cod2', { stat_cod2: EITZJC })
                    }),
                )
                .orderBy('stat_tim', 'ASC')
                .getRawMany();
                for (let statIndex = 0; statIndex < cntrStats.length; statIndex++) {
                    const stat = cntrStats[statIndex];
                    if (stat.current_stat === '1') {
                        calObj.start = dayjs(stat.stat_tim);
                        if (dayjs(stat.stat_tim).isAfter(dayjs(stackEndTim))) {  // 动态时间大于统计截止时间
                            break;
                        }
                    }
                    if (stat.current_stat === '2') {
                        if (dayjs(stat.stat_tim).isBefore(dayjs(stackStartTim))) { // 动态时间早于统计开始时间，在统计开始时间前已经返场
                            calObj.start = null;
                            calObj.end = null;
                        } else {
                            calObj.end = dayjs.min(dayjs(stat.stat_tim), dayjs(stackEndTim));
                            if (!isNil(calObj.start)) {  
                                calObj.start = dayjs.max(calObj.start, dayjs(stackStartTim));
                                const days = calObj.end.startOf('day').diff(calObj.start.startOf('day'), 'day') + 1;
                                if (days > 0) {
                                    value += days;
                                }
                                calObj.end = null;
                            }
                            calObj.start = null;
                        }
                    }
                }
                if (isNil(calObj.end)) {
                    calObj.end = dayjs(stackEndTim);
                }
                if (!isNil(calObj.start)) {  
                    calObj.start = dayjs.max(calObj.start, dayjs(stackStartTim));
                    const days = calObj.end.startOf('day').diff(calObj.start.startOf('day'), 'day') + 1;
                    if (days > 0) {
                        value += days;
                    }
                    calObj.end = null;
                }
            }
            leaseCntr.push({
                month: months[index], value: value.toString()
            })
        } */
        return {
            code: ResponseCode.SuccessNoMsg,
            obj: {
                inCntr,
                outCntr,
                stackCntr,
                leaseCntr
            },
            ext: months
        }
    }
    async createReleaseFormPDF(
        dto: ReleaseFormDto,
        oper_nam: string,
    ): Promise<ResponseJson<string, null>> {
        // console.log('cntr_info', dto.cntr_info);


        const plan = await this.datasource
            .createQueryBuilder<CntrPlan>(CntrPlan, 'truck_cntr_plan')
            .where('id = :id', { id: dto.plan_id })
            .getOne();

        const printednum = await this.datasource
            .createQueryBuilder<LeaseForm>(LeaseForm, 'release_form')
            .select('ifnull(sum(cntr_num), 0) as num')
            .where('plan_id = :planid', { planid: dto.plan_id })
            .getRawOne();

        if (parseInt(printednum.num) + dto.cntr_num > plan.cntr_num) {
            throw new Error('已打印箱量超过总箱量xxx');
        }

        const cfs = await this.datasource
            .createQueryBuilder<CyCfs>(CyCfs, 'c_cy_cfs')
            .where('id = :id', { id: plan.cfs_cod })
            .getOne();

        dto.cntr_siz_cod = plan.cntr_siz_cod;
        dto.cntr_typ_cod = plan.cntr_typ_cod;
        // dto.dam_id = plan.dam_id;
        dto.oper_nam = oper_nam;
        dto.release_no = plan.bill_no;
        dto.create_tim = new Date();
        dto.cfs_cod = plan.cfs_cod;
        // 处理excel文件

        const workbook = new Excel.Workbook();
        await workbook.xlsx.readFile(
            join(this.downloadpath, `${dto.form_style}.xlsx`),
        );
        const worksheet = workbook.getWorksheet(1);

        /* const bill_no_cell = worksheet.getCell('C4');
        bill_no_cell.value = `Release 单号：${dto.release_no}`; */
        const title_cell = worksheet.getCell('A1');
        title_cell.value = `Container Release Order
放箱单
Release 单号：${dto.release_no}`
        const created_tim_cell = worksheet.getCell('B5');
        created_tim_cell.value = dayjs(dto.create_tim).format('YYYY-MM-DD');
        const cntrsiztyp_cell = worksheet.getCell('A10');
        cntrsiztyp_cell.value = `${dto.cntr_siz_cod}${dto.cntr_typ_cod}`;
        const damcod_cell = worksheet.getCell('C10');
        damcod_cell.value = dto.dam_id;
        const cntr_num_cell = worksheet.getCell('E10');
        cntr_num_cell.value = dto.cntr_num;
        const cntr_info_cell = worksheet.getCell('F10');
        cntr_info_cell.alignment = {
            wrapText: true,
            vertical: 'middle',
            horizontal: 'center',
        };
        cntr_info_cell.value = dto.cntr_info;
        const lines = dto.cntr_info.split('\n').length;
        const row = worksheet.getRow(10);
        row.height = 14 * (lines ?? 1);
        const remark_cell = worksheet.getCell('A17');
        remark_cell.value = `提柜地点：${cfs.cfs_nam_c}\n地址：${cfs.address}\n${cfs.remark}\n联系人: ${cfs.linkman}`;
        const newfilename = `${uuidv4()}.xlsx`;
        const inputPath = join(this.downloadpath, newfilename);
        await workbook.xlsx.writeFile(inputPath);

        const filepath = join(
            this.downloadpath,
            'release_pdf',
            dayjs(new Date()).format('YYYYMM'),
        );
        if (!existsSync(filepath)) {
            mkdirSync(filepath, { recursive: true });
        }
        const filename = `${uuidv4()}.pdf`;

        const outputPath = join(filepath, filename);
        await this.OfficeToPdf(inputPath, outputPath);
        dto.filepath = `/download/release_pdf/${dayjs(new Date()).format(
            'YYYYMM',
        )}/${filename}`;
        await this.createLeaseForm(dto);
        unlinkSync(join(this.downloadpath, newfilename)); // 删除生成的临时excel文件
        // 发送邮件
        const mailOption: EmailType = {};

        const corpmail = await this.datasource.createQueryBuilder()
            .select(['email', 'client_nam_c'])
            .from(Client, 'c_client_cod')
            .where('id = :id', { id: dto.cntr_corp_cod })
            .getRawOne();

        const cfsmail = await this.datasource.createQueryBuilder()
            .select(['email', 'cfs_nam_c'])
            .from(CyCfs, 'c_cy_cfs')
            .where('id = :id', { id: dto.cfs_cod })
            .getRawOne();

        if (corpmail?.email && cfsmail?.email) {
            mailOption.to = `${corpmail['email']};${cfsmail['email']}`;
            mailOption.subject = '放箱单';
            mailOption.text = `放箱单`;
            mailOption.attachments = [
                {
                    filename: filename,
                    path: outputPath
                }
            ]
            const sendR = await this.sharedService.sendMail(mailOption);
        }
        return {
            code: ResponseCode.SuccessNoMsg,
            obj: dto.filepath,
        };
        /* if (result.data.code === 0) {
            return {
                code: ResponseCode.SuccessNoMsg,
            }
        } else {
            return {
                code: ResponseCode.FailWithMsg,
                msg: ['远程转换PDF失败']
            }    
        } */
    }
    async OfficeToPdf(inputPath: string, outputPath: string) {
        const ext = '.pdf';
        // const inputPath = path.join(__dirname, 'logo.xlsx');
        // const outputPath = path.join(__dirname, `logo${ext}`);

        // Read file
        const docxBuf = readFileSync(inputPath);

        // Convert it to pdf format with undefined filter (see Libreoffice docs about filter)
        let pdfBuf = await (libre as any)['convertAsync'](
            docxBuf,
            ext,
            undefined,
        );

        // Here in done you have pdf file which you can save or transfer in another stream
        await writeFileSync(outputPath, pdfBuf);
    }
    async createInvoicePDF(
        invoice_id: number
    ): Promise<ResponseJson<string, null>> {
        const invoice = await this.datasource
            .createQueryBuilder<Invoice>(Invoice, 'invoice')
            .leftJoinAndSelect('invoice.details', 'invoice_detail')
            .where('invoice.id = :id', { id: invoice_id })
            .getOne();
        // console.log('invoice', invoice);

        // 处理excel文件

        const workbook = new Excel.Workbook();
        await workbook.xlsx.readFile(
            join(this.downloadpath, 'invoice.xlsx'),
        );


        const worksheet = workbook.worksheets[0];
        // console.log('worksheet', worksheet);

        const from_to_corp_cell = worksheet.getCell('C10');
        from_to_corp_cell.value = `${invoice.from_corp_nam}   TO:  ${invoice.to_corp_nam}`;
        const invoice_no_cell = worksheet.getCell('B11');
        invoice_no_cell.value = `${invoice.invoice_no}`;
        const invoice_date_cell = worksheet.getCell('B12');
        invoice_date_cell.value = `${dayjs(invoice.invoice_date).format('YYYY-MM-DD')}`;
        const currency_cell = worksheet.getCell('B14');
        currency_cell.value = `${invoice.currency_cod}`;
        const excelrow = 20
        for (let index = 0; index < invoice.details.length; index++) {
            const detail = invoice.details[index];
            const type_cell = worksheet.getCell(`A${excelrow + index}`);
            type_cell.value = `${detail.cntr_typ}`;
            const status_cell = worksheet.getCell(`B${excelrow + index}`);
            status_cell.value = `${detail.status}`;
            const description_cell = worksheet.getCell(`C${excelrow + index}`);
            description_cell.value = `${detail.description}`;
            const qty_cell = worksheet.getCell(`D${excelrow + index}`);
            qty_cell.value = `${detail.qty}`;
            const price_cell = worksheet.getCell(`E${excelrow + index}`);
            price_cell.value = `${detail.price}`;
            const amount_cell = worksheet.getCell(`F${excelrow + index}`);
            amount_cell.value = `${detail.amount}`;
        }
        const total_cell = worksheet.getCell(`F38`);
        total_cell.value = `${invoice.amount}`;


        const newfilename = `${uuidv4()}.xlsx`;
        const inputPath = join(this.downloadpath, newfilename);
        await workbook.xlsx.writeFile(inputPath);

        const filepath = join(
            this.downloadpath,
            'invoice_pdf',
            dayjs(new Date()).format('YYYYMM'),
        );
        if (!existsSync(filepath)) {
            mkdirSync(filepath, { recursive: true });
        }
        const filename = `${uuidv4()}.pdf`;

        const outputPath = join(filepath, filename);
        await this.OfficeToPdf(inputPath, outputPath);
        const downloadpath = `/download/invoice_pdf/${dayjs(new Date()).format(
            'YYYYMM',
        )}/${filename}`;

        unlinkSync(join(this.downloadpath, newfilename)); // 删除生成的临时excel文件
        return {
            code: ResponseCode.SuccessNoMsg,
            obj: downloadpath,
        };

    }
    async createLeaseForm(
        releaseFormObj: ReleaseFormDto,
        q?: QueryRunner,
    ): Promise<number> {
        const queryRunner = q ?? this.datasource.createQueryRunner();

        const errors = await validate(
            plainToInstance(ReleaseFormDto, releaseFormObj),
        );
        if (errors.length > 0) {
            throw new Error(Object.values(errors[0].constraints).join('   '));
        }

        const insertResult = await queryRunner.manager
            .createQueryBuilder()
            .insert()
            .into(LeaseForm)
            .values(releaseFormObj)
            .execute();

        if (insertResult.raw.affectedRows === 1) {
            return insertResult.raw.insertId;
        } else {
            return 0;
        }
    }
    async writeToFile(url: string, filepath: string, filename: string) {
        return new Promise(async (resolve, reject) => {
            // 发送 GET 请求
            const response = await axios({
                method: 'GET',
                url: url,
                responseType: 'stream', // 设置响应类型为 'stream'，以便保存到文件
            });

            // 创建文件写入流
            const writer = createWriteStream(join(filepath, filename));
            writer.on('finish', resolve); // 正确
            writer.on('error', reject); // 监听错误事件
            // 将响应数据通过流写入文件
            response.data.pipe(writer);
        });
    }

}
